update a a1
set name = ( select name from b where a.id=b.id)
where rowid = ( select min(rowid) from a a2 where a2.id=a1.id);
set name = ( select name from b where a.id=b.id)
where rowid = ( select min(rowid) from a a2 where a2.id=a1.id);
调试欢乐多
where rowid in ( select min(rowid) from a group by id );
该语句有两个运行前提:
1. b的id为唯一健或主键
2. b的id取值范围要比a大,即a的id字段以b的id字段为外键。如果上述均条件不成立,则可以试试下述语句:
update a set name = ( select max(name) from b where a.id = b.id )
where rowid in ( select min(rowid) from a where a.id in ( select distinct id from b ) group by id;如果你只是想a的将address更新到name,那么可以写为:
update a set name = address
where rowid in ( select min(rowid) from a where a.id in ( select distinct id from b ) group by id;