MySQL联表更新

下面是两张表的结构

mysql> SELECT * FROM product;
+----+-----------+-----------------------+-------+----------+
| id | productid | productname           | price | isdelete |
+----+-----------+-----------------------+-------+----------+
|  1 |      1001 | C语言中文网Java教程   |   100 |        0 |
|  2 |      1002 | C语言中文网MySQL教程  |   110 |        0 |
|  3 |      1003 | C语言中文网Python教程 |   120 |        0 |
|  4 |      1004 | C语言中文网C语言教程  |   150 |        0 |
|  5 |      1005 | C语言中文网GoLang教程 |   160 |        0 |
+----+-----------+-----------------------+-------+----------+
5 rows in set (0.02 sec)

mysql> SELECT * FROM product_price;
+----+-----------+-------+
| id | productid | price |
+----+-----------+-------+
|  1 |      1001 |  NULL |
|  2 |      1002 |  NULL |
|  3 |      1003 |  NULL |
|  4 |      1004 |  NULL |
|  5 |      1005 |  NULL |
+----+-----------+-------+
5 rows in set (0.01 sec)

一、UPDATE

mysql> UPDATE product p, product_price pp SET pp.price = p.price * 0.8 WHERE p.productid= pp.productId;
Query OK, 5 rows affected (0.02 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> SELECT * FROM product_price;
+----+-----------+-------+
| id | productid | price |
+----+-----------+-------+
|  1 |      1001 |    80 |
|  2 |      1002 |    88 |
|  3 |      1003 |    96 |
|  4 |      1004 |   120 |
|  5 |      1005 |   128 |
+----+-----------+-------+
5 rows in set (0.00 sec)

二、INNER JOIN

mysql> UPDATE product p INNER JOIN product_price pp ON p.productid= pp.productid SET pp.price = p.price * 0.8;
Query OK, 5 rows affected (0.09 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> SELECT * FROM product_price;
+----+-----------+-------+
| id | productid | price |
+----+-----------+-------+
|  1 |      1001 |    80 |
|  2 |      1002 |    88 |
|  3 |      1003 |    96 |
|  4 |      1004 |   120 |
|  5 |      1005 |   128 |
+----+-----------+-------+
5 rows in set (0.00 sec)

三、LEFT JOIN

使用LEFT JOIN要注意如果右表没有左边对应的数据则会将数据置为NULL。

mysql> UPDATE product p LEFT JOIN product_price pp ON p.productid= pp.productid SET p.isdelete = 1 WHERE pp.productid IS NULL;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM product;
+----+-----------+-----------------------+-------+----------+
| id | productid | productname           | price | isdelete |
+----+-----------+-----------------------+-------+----------+
|  1 |      1001 | C语言中文网Java教程   |   100 |        0 |
|  2 |      1002 | C语言中文网MySQL教程  |   110 |        0 |
|  3 |      1003 | C语言中文网Python教程 |   120 |        0 |
|  4 |      1004 | C语言中文网C语言教程  |   150 |        0 |
|  5 |      1005 | C语言中文网GoLang教程 |   160 |        0 |
|  6 |      1006 | C语言中文网Spring教程 |  NULL |        1 |
+----+-----------+-----------------------+-------+----------+
6 rows in set (0.00 sec)

四、子查询

mysql> UPDATE product_price pp SET price=(SELECT price*0.8 FROM product WHERE productid = pp.productid);
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> SELECT * FROM product_price;
+----+-----------+-------+
| id | productid | price |
+----+-----------+-------+
|  1 |      1001 |    80 |
|  2 |      1002 |    88 |
|  3 |      1003 |    96 |
|  4 |      1004 |   120 |
|  5 |      1005 |   128 |
+----+-----------+-------+
5 rows in set (0.00 sec)

五、SELECT INTO用于复制表数据

转载文章MySQL UPDATE多表关联更新