--------更新-------
方法一:子查询更新
update B set
name=(select name from A where id=B.id),
sex = (select sex from A where id=B.id)
方法二:游标更新
declare @id int
declare @name nvarchar(50)
declare @sex int
declare cur cursor for
select
id,
name,
sex
from A
open cur
fetch next from cur into
@id,
@name,
@sex
while @@fetch_status=0
begin
update B set
name=@name,
sex=@sex
where id=@id
fetch next from cur into
@id,
@name,
@sex
end
close cur
deallocate cur
方法三:关联更新(效率最优)sql server支持这种写法
update b set
name=a.name,
sex=a.sex
from B as b
inner join A as a on(a.id=b.id)
// mysql支持的语法
UPDATE tablename1,tablename2 SET tablename1.id= tablename2.id
WHERE tablename1.name = tablename2.name;
------插入------
insert into B
(
name,
sex
)
select name,sex from A