同一表内UPDATE数据
mysql> select * from test1; +----+------+------+------+------+------+ | id | a | b | c | d | e | +----+------+------+------+------+------+ | 1 | 34 | 6 | 10 | 0 | 2 | | 2 | 22 | 5 | 22 | 0 | 5 | | 3 | 94 | 10 | 34 | 0 | 9 | | 4 | 34 | 10 | 4 | 0 | NULL | | 5 | 84 | 7 | 8 | 0 | NULL | | 6 | 85 | 10 | 57 | 0 | NULL | | 7 | 60 | 10 | 10 | 0 | NULL | | 8 | 34 | 10 | 10 | 0 | NULL | | 9 | 57 | 6 | 22 | 0 | NULL | | 10 | 33 | 9 | 10 | 0 | NULL | | 11 | 90 | 7 | 4 | 0 | NULL | | 12 | 20 | 10 | 57 | 0 | NULL | +----+------+------+------+------+------+
update test1 set e=d where id >3;
mysql> select * from test1; +----+------+------+------+------+------+ | id | a | b | c | d | e | +----+------+------+------+------+------+ | 1 | 34 | 6 | 10 | 0 | 2 | | 2 | 22 | 5 | 22 | 0 | 5 | | 3 | 94 | 10 | 34 | 0 | 9 | | 4 | 34 | 10 | 4 | 0 | 0 | | 5 | 84 | 7 | 8 | 0 | 0 | | 6 | 85 | 10 | 57 | 0 | 0 | | 7 | 60 | 10 | 10 | 0 | 0 | | 8 | 34 | 10 | 10 | 0 | 0 | | 9 | 57 | 6 | 22 | 0 | 0 | | 10 | 33 | 9 | 10 | 0 | 0 | | 11 | 90 | 7 | 4 | 0 | 0 | | 12 | 20 | 10 | 57 | 0 | 0 | +----+------+------+------+------+------+
两个表之间UPDATE数据
mysql> select * from test2; +----+------+------+ | id | f | g | +----+------+------+ | 1 | 2 | NULL | | 2 | 5 | NULL | | 3 | 9 | NULL | +----+------+------+
update test2 join test1 on test1.id = test2.id set test2.g=test1.d;
mysql> select * from test2; +----+------+------+ | id | f | g | +----+------+------+ | 1 | 2 | 0 | | 2 | 5 | 0 | | 3 | 9 | 0 | +----+------+------+
还可以利用 LEFT JOIN 实现增量数据的 INSERT INTO 插入
先贴链接 https://blog.csdn.net/weixin_42659958/article/details/90298791
标签：test1,10,+----+------+------+,Update,34,NULL,数据,id From： https://www.cnblogs.com/conpi/p/16748306.html