将table_b中的数据更新到table_a中指定的数据中去

UPDATE

     table_a ta,(SELECT tb_value1,tb_value2 FROM table_b WHERE tb_value3 = 'value3') tb

SET ta.ta_value1 = tb.tb_value1 

WHERE ta.ta_value2 = tb.tb_value2;

需求 

有表成绩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;

mysql更新一张表的数据到另一张表

 

解决方法：

例如，把tb_account表的ac_name字段的值更新到tb_account_channelgroup表（存有tb_account表的ac_id字段，也就是说tb_account是一方，tb_account_channelgroup是多方）的scg_name字段

sql:http://www.yayihouse.com/yayishuwu/chapter/1902

1.查询出数据更新到另一张表

需求：从A表查询出字段数据，更新到B表对应的字段

示例：当table_a表中的b_id=table_b表的id时，将table_b表中的sn,filed1更新到table_a 表中sn,filed1字段

-- 更新数据
UPDATE `table_a` r
INNER JOIN (SELECT id,sn,filed1 FROM `table_b`) t ON r.b_id= t.id
SET r.sn = t.sn,r.filed1 = t.filed1 

2.一张表查询出数据插入到另一张

insert into table_a(字段1,字段2,字段3,....)  select a,b,c,.... from table_b where 条件 

示例：

insert into table_a(`name`,`age`,`gender`) select b_name,b_age,b_gender from table_b where id = 5;

说明：

1、table_a表插入的字段个数必须同select后面跟的字段个数一致

2、where条件可以写多个

3、select后面跟的字段可以写固定值（部分写死，全部写死相当于直接insert,这样就没必要查询）

示例：insert into table_a(`name`,`age`,`gender`) select '张三', 18, b_gender from table_b where id = 5;