oracle上
有两个表
如语句：
update tableA set tableA.line=tableB.line where tableA.id=tableB.id;
因为有许多行，不知道怎么写这个小的过程~~
就是tableA上有几十行需要从tableB上update的
谢谢了~~
------解决方案--------------------
update tableA set tableA.line=(selecy tableB.line from tableB where tableA.id=tableB.id) where exists (select * from tableb where tablea.id=tableb.id);
若ID是主键可以这样
UPDATE (SELECT A.COL COLA,B.COL COLB FROM tableA A,tableB B WHERE A.ID=B.ID) SET COLA=COLB
------解决方案--------------------
如果tableB的ID是PK，可以
update (select a.line,b.line from a,b where a.id=b.id) set a.line=b.line;
否则就
update a set a.line=(select b.line from b where b.id=a.id and rownum=1) where exists(select 1 from b where a.id=b.id);
------解决方案--------------------
b.id不是主键不能使用update (select a.line,b.line from a,b where a.id=b.id) set a.line=b.line.这个不知道后台到底是怎么实现的，但是这种写法比update a set a.line=(select b.line from b where b.id=a.id and rownum=1) where exists(select 1 from b where a.id=b.id);慢,根据执行计划来看,前者是
UPDATE STATEMENT, GOAL = ALL_ROWS Cost=4 Cardinality=3 Bytes=186
UPDATE Object owner=WPUSH Object name=A
NESTED LOOPS Cost=4 Cardinality=3 Bytes=186
TABLE ACCESS FULL Object owner=WPUSH Object name=A Cost=3 Cardinality=3 Bytes=93
TABLE ACCESS BY INDEX ROWID Object owner=WPUSH Object name=B Cost=1 Cardinality=1 Bytes=31
INDEX UNIQUE SCAN Object owner=WPUSH Object name=SYS_C0017314 Cost=0 Cardinality=1
后者是:
UPDATE STATEMENT, GOAL = ALL_ROWS Cost=3 Cardinality=3 Bytes=93
UPDATE Object owner=WPUSH Object name=A
TABLE ACCESS FULL Object owner=WPUSH Object name=A Cost=3 Cardinality=3 Bytes=93
TABLE ACCESS BY INDEX ROWID Object owner=WPUSH Object name=B Cost=1 Cardinality=1 Bytes=31
INDEX UNIQUE SCAN Object owner=WPUSH Object name=SYS_C0017314 Cost=0 Cardinality=1