表sheep1
id | cname |
1 | a |
2 | b |
3 | c |
表sheep2
id | cname |
1 | x |
2 | y |
3 | z |
1.对于mysql
update sheep1,sheep2 set sheep2.cname=sheep1.cname where sheep1.id=sheep2.id
2.对于oracle
update sheep2 set sheep2.cname=(select cname from sheep1 where sheep1.id=sheep2.id)
上述语句有时候能成功,有时候报错single-row subquery returns more than one row
此时改为
update sheep2 set sheep2.cname=(select max(cname) from sheep1 where sheep1.id=sheep2.id) where exists(select * from sheep1 where sheep1.id=sheep2.id)
原因还在研究,此语法效率很低,如果使用存储过程可以使用游标代替。
update sheep2 set cname=sheep1.cname from sheep1 where sheep1.id=sheep2.id
以上语句亲测可用,鄙视未经验证胡乱转发的小人,如以上语句有错请留言