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 T2
ON (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) T2
ON (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 T2
ON (T1.PID = T2.PID AND T1.ISMinority = 'Y')
WHEN MATCHED THEN UPDATE SET T1.SCORE = T1.SCORE + T2.ADD_SCORE;
COMMIT;