本文介绍了从其他表更新mysql表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有两张桌子.
表1
a - b - c- d - e
1---b1---c1---d1---e1
2---b2---c2---d2---e2
3---b3---c3---d3---e3
4---b4---c4---d4---e4
5---b5---c5---d5---e5
table2
a----b----c----d----e
1---b2---c2---d2---e2
3---b3---c3---d3---e3
5---b5---c5---d5---e5
6---b6---c6---d6---e6
有关 table1 的某些信息未包含在 table2 中 - 所以我需要将 table 2 更新为 table2 的副本.我试过
some information on the table1 is not included in table2 - so i need to update table 2 as a copy of table2. i tried
UPDATE table1 t1, table2 t2 SET t2.b = t1.b, t2.c = t1.c, t2.d = t1.d
但是有 0 行受到影响 - 没有进行任何更改.我还能做什么?
but 0 rows affected - not any change have made. what can i do else?
推荐答案
首先:你需要从table1中插入那些没有出现在table2中的记录:
First: You need to insert those records that are not presented in the table2 from the table1:
INSERT INTO table2(a, b, c, d, e)
SELECT t1.*
FROM table1 AS t1
LEFT JOIN table2 AS t2 ON t1.a = t2.a
WHERE t2.a IS NULL;
然后UPDATE
它们匹配table1:
Then UPDATE
them to match table1:
UPDATE table1 t1
INNER JOIN table2 t2 AS t1.a = t2.a
SET t2.b = t1.b,
t2.c = t1.c,
t2.d = t1.d,
t2.e = t2.e;
这篇关于从其他表更新mysql表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!