oracle上
有两个表
如语句:
update tableA set tableA.line=tableB.line where tableA.id=tableB.id;因为有许多行,不知道怎么写这个小的过程~~
就是tableA上有几十行需要从tableB上update的谢谢了~~
有两个表
如语句:
update tableA set tableA.line=tableB.line where tableA.id=tableB.id;因为有许多行,不知道怎么写这个小的过程~~
就是tableA上有几十行需要从tableB上update的谢谢了~~
UPDATE (SELECT A.COL COLA,B.COL COLB FROM tableA A,tableB B WHERE A.ID=B.ID) SET COLA=COLB
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);
tableB 没设置主键
这样有关系吗?是这样的,tableB是我临时建的表,里面的数据用于更新到tableA,根据两个表的id来update的
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