Oracle 将一张表的数据更新到另一张表

• 需求
  有表成绩T1，记录学生成绩，表T2是一张加分的表，现在用表T2给少数名族学生加分。
• 表成绩表T1

 PID      SCORE ISMINORITY---- ---------- ----------   1        670 N   2        620 N   3        600 N   4        520 Y   5        480 N   6        568 Y

• 表T2（加分表）

 PID  ADD_SCORE---- ----------   3         21   4        100   6        100

• 写法1（可能有潜在error）

MERGE INTO T1 USING T2ON (T1.PID = T2.PID)WHEN MATCHED THEN UPDATE SET T1.SCORE = T1.SCORE + T2.ADD_SCORE WHERE T1.ISMinority = 'Y';

写法1有潜在error是因为Merge 不能多次更新被更新表（即T1）的同一条记录，如果T2有重复的数据，比如两条pid为4的记录。那么写法1将报错
ORA-30926: unable to get a stable set of rows in the source tables
但是如果T2有两条pid为3则不会报错

• 升级版写法

MERGE INTO T1 USING (SELECT PID, MAX(add_score) add_score FROM t2 GROUP BY PID) T2ON (T1.PID = T2.PID)WHEN MATCHED THEN UPDATE SET T1.SCORE = T1.SCORE + T2.ADD_SCORE WHERE T1.ISMinority = 'Y';

• 完整代码

 CREATE TABLE T1(PID INT, SCORE INT, ISMINORITY VARCHAR(1));CREATE TABLE T2(PID INT, ADD_SCORE INT);INSERT INTO T1 VALUES(1,670,'N');INSERT INTO T1 VALUES(2,620,'N');INSERT INTO T1 VALUES(3,600,'N');INSERT INTO T1 VALUES(4,520,'Y');INSERT INTO T1 VALUES(5,480,'N');INSERT INTO T1 VALUES(6,568,'Y');INSERT INTO T2 VALUES(3,21);INSERT INTO T2 VALUES(4,21);INSERT INTO T2 VALUES(6,21);COMMIT;SELECT * FROM T1;MERGE INTO T1 USING T2ON (T1.PID = T2.PID AND T1.ISMinority = 'Y')WHEN MATCHED THEN UPDATE SET T1.SCORE = T1.SCORE + T2.ADD_SCORE;COMMIT;