update table2 set name=(select code from table1 where table1.id=table2.id) where exists (select 1 from table1 where table1.id=table2.id);
update (select t2.id,t2.name from table2 t2,table1 t1 where t1.id = t2.id and t1.code = 'xxxx') k set k.name = 'yyyyy'
楼上的大哥没有完全理解,我说的根据code来修改name 如 表1数据 id code 1 ab 表2数据 pid id name a 1 sss b 1 ddd 最终目的是根据ab来修改name 不是将name修改为code 就是根据ab 来把表2 中的两条记录的name都修改成fff
SQL> drop table table1;Table droppedSQL> create table table1 as select 1 id, 'ab' code from dual;Table createdSQL> drop table table2;Table droppedSQL> SQL> create table table2 as select 'a' pid, 1 id, 'sss' name 2 from dual 3 union all 4 select 'b' pid, 1 id, 'ddd' name from dual 5 /Table createdSQL> select * from table2;PID ID NAME --- ---------- ---- a 1 sss b 1 dddSQL> update table2 set name = 'fff' where exists (select 1 from table1,table2 where table1.id = table2.id and table1.code = 'ab');2 rows updatedSQL> select * from table2;PID ID NAME --- ---------- ---- a 1 fff b 1 fffSQL>
where exists (select 1 from table1 where table1.id=table2.id);
update (select t2.id,t2.name from table2 t2,table1 t1 where t1.id = t2.id and t1.code = 'xxxx') k set k.name = 'yyyyy'
如 表1数据
id code
1 ab
表2数据
pid id name
a 1 sss
b 1 ddd
最终目的是根据ab来修改name 不是将name修改为code
就是根据ab 来把表2 中的两条记录的name都修改成fff
SQL> drop table table1;Table droppedSQL> create table table1 as select 1 id, 'ab' code from dual;Table createdSQL> drop table table2;Table droppedSQL>
SQL> create table table2 as select 'a' pid, 1 id, 'sss' name
2 from dual
3 union all
4 select 'b' pid, 1 id, 'ddd' name from dual
5 /Table createdSQL> select * from table2;PID ID NAME
--- ---------- ----
a 1 sss
b 1 dddSQL> update table2 set name = 'fff' where exists (select 1 from table1,table2 where table1.id = table2.id and table1.code = 'ab');2 rows updatedSQL> select * from table2;PID ID NAME
--- ---------- ----
a 1 fff
b 1 fffSQL>