使用Oracle 10g MERGE语句更新数据行

DROP TABLE open_projects; 

DROP TABLE project_updates; 

CREATE TABLE open_projects 

(pno NUMBER(6) PRIMARY KEY, 

title VARCHAR2(40), 

startdate DATE, 

pctdone NUMBER(3), 

empno NUMBER(6) 

); 

INSERT INTO open_projects VALUES 

(10, 'Inventory servers', '08-JAN-07',0, 206); 

INSERT INTO open_projects VALUES 

(20, 'Upgrade Oracle on SRV01','15-JAN-07', 0, 206); 

INSERT INTO open_projects VALUES 

(30, 'Conduct skills assessment','22-JAN-07', 0, 210); 

CREATE TABLE project_updates 

(action CHAR(1), 

pno NUMBER(6), 

pctdone NUMBER(3), 

empno NUMBER(6) 

); 

INSERT INTO project_updates VALUES 

('C', 10, 50, 214); 

INSERT INTO project_updates VALUES 

('D', 20, NULL, NULL); 

COMMIT;

MERGE INTO open_projects op 

USING project_updatespu 

ON (op.pno = pu.pno) 

...

... 

WHEN MATCHED THEN 

UPDATE SET pctdone = pu.pctdone, 

empno = pu.empno 

DELETE 

WHERE pu.action = 'D';

SQL> @mergedel_b 

PNO TITLE STARTDATE PCTDONE EMPNO 

---------- ---------------------------------------- 

10 Inventory servers 08-JAN-07 0 206 

20 Upgrade Oracle on SRV01 15-JAN-07 0 206 

30 Conduct skills assessment 22-JAN-07 0 210 

A PNO PCTDONE EMPNO 

- ---------- ---------- ---------- 

C 10 50 214 

D 20 

2 rows merged. 

PNO TITLE STARTDATE PCTDONE EMPNO 

---------- ----------------------------------------  

10 Inventory servers 08-JAN-07 50 214 

30 Conduct skills assessment 22-JAN-07 0 210 

A PNO PCTDONE EMPNO 

- ---------- ---------- ---------- 

C 10 50 214 

D 20 

SQL> spool off