17,078
社区成员
发帖
与我相关
我的任务
分享
UPDATE A
SET (DEALID,
ORDERID,
NAME,
DBCOLUMNNAME,
DATATYPE,
DATALENGTH,
SCALE,
CSID,
ISPRIMARY,
ISRESERVE,
GUID,
STATUS,
DBVERSION,
DEFAULTVALUE,
ISLOGICKEY,
DEID)
=(SELECT DEALID,
ORDERID,
NAME,
DBCOLUMNNAME,
DATATYPE,
DATALENGTH,
SCALE,
CSID,
ISPRIMARY,
ISRESERVE,
GUID,
STATUS,
DBVERSION,
DEFAULTVALUE,
ISLOGICKEY,
DEID
FROM A1
WHERE EXISTS
(SELECT 1
FROM A
WHERE A1.GUID = A.GUID
AND A1.DEALID = A.DEALID))
-- 可以考虑使用 merge
SQL> create table a1(id int , v1 int , v2 int);
Table created
SQL> create table a (id int , v1 int , v2 int);
Table created
SQL> begin
2 insert into a1 values(1,11,21) ;
3 insert into a1 values(2,12,22) ;
4 insert into a1 values(3,13,23) ;
5 insert into a1 values(4,14,24) ;
6
7 insert into a values(1,111,121) ;
8 insert into a values(2,121,222) ;
9 commit ;
10 end ;
11 /
PL/SQL procedure successfully completed
SQL> select * from a1 ;
ID V1 V2
---------- ---------- ----------
1 11 21
2 12 22
3 13 23
4 14 24
SQL> select * from a ;
ID V1 V2
---------- ---------- ----------
1 111 121
2 121 222
SQL> merge into a using a1 on (a.id = a1.id)
2 when matched then
3 update set a.v1 = a1.v1,a.v2 = a1.v2
4 when not matched then
5 insert (id,v1,v2) values(a1.id,a1.v1,a1.v2);
4 rows merged
SQL> /
4 rows merged
SQL> select * from a1 ;
ID V1 V2
---------- ---------- ----------
1 11 21
2 12 22
3 13 23
4 14 24
SQL> select * from a ;
ID V1 V2
---------- ---------- ----------
1 11 21
2 12 22
4 14 24
3 13 23
SQL> drop table a1 purge ;
Table dropped
SQL> drop table a purge ;
Table dropped