update tableb set b=(select b from tablea where tableb.a=tablea.a)
update tableb x set b = (select b from tablea y a where x.a = y.a) where exists(select * from tablea y a where x.a = y.a)???
update 表名 set 字段=值 where 条件
SQL> select * from tablea; A B ---------- ---------- 1 3 2 2 3 3 5 3SQL> select * from tableb; A B C ---------- ---------- ---------- 1 1 1 1 1 2 1 2 2 2 1 2 2 2 3 3 1 1 4 2 37 rows selectedSQL> update tableb set b=(select b from tablea where tableb.a=tablea.a) 2 ;7 rows updated
建议使用wylwyl1130(落雪山林) 的写法update tableb x set b = (select b from tablea y a where x.a = y.a) where exists(select * from tablea y a where x.a = y.a);否则,如果有对应不上的数据,会更新为null。
update tableb set tableb.b=(select b from tablea where tableb.a=tablea.a) where exists(select 1 from tablea where tableb.a=tablea.a)
update tableb x set b=(select b from tablea y where x.a=y.a) where exists(select 'X' from tablea z where x.a=z.a)
where exists(select * from tablea y a where x.a = y.a)???
---------- ----------
1 3
2 2
3 3
5 3SQL> select * from tableb; A B C
---------- ---------- ----------
1 1 1
1 1 2
1 2 2
2 1 2
2 2 3
3 1 1
4 2 37 rows selectedSQL> update tableb set b=(select b from tablea where tableb.a=tablea.a)
2 ;7 rows updated
where exists(select * from tablea y a where x.a = y.a);否则,如果有对应不上的数据,会更新为null。
set b=(select b from tablea y where x.a=y.a)
where exists(select 'X' from tablea z where x.a=z.a)