Update 数据

同一表内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