mysql update join sql语句总结

最新推荐文章于 2021-11-02 18:39:49 发布

原创最新推荐文章于 2021-11-02 18:39:49 发布 · 1.2k 阅读

0 ·

CC 4.0 BY-SA版权

本文介绍了MySQL中如何通过JOIN操作实现多表之间的更新操作，包括使用INNER JOIN和LEFT OUTER JOIN来更新价格等字段的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

首先注意的是你用的是mysql还是sqlserver

此处是针对mysql 进行join进行更新的

mysql> select * from goods;
+------+----------+-------+--------+
| id | name | price | cat_id |
+------+----------+-------+--------+
| 9 | meizu | 67 | 33 |
| 1 | pingguo | 30 | 11 |
| 2 | pear | 33 | 22 |
| 3 | tao | 34 | 33 |
| 4 | apple | 35 | 11 |
| 5 | hetao | 30 | 22 |
| 6 | huasheng | 33 | 33 |
| 7 | xiaomi | 55 | 11 |
| 8 | huawei | 65 | 22 |
+------+----------+-------+--------+

mysql> select * from sales;
+------+------+--------+
| id | cnt | cat_id |
+------+------+--------+
| 1 | 1 | 11 |
| 2 | 2 | 22 |
| 3 | 3 | 33 |
+------+------+--------+

update sales a join goods b on a.id=b.id set a.cat_id=b.cat_id ;

假定我们有两张表，一张表为Product表存放产品信息，其中有产品价格列Price；另外一张表是ProductPrice表，我们要将ProductPrice表中的价格字段Price更新为Price表中价格字段的80%。

在MySQL中我们有几种手段可以做到这一点，一种是update table1 t1, table2 ts ...的方式：

<span class="pln" style="font-family: Arial, 宋体;">UPDATE product p</span><span class="pun" style="font-family: Arial, 宋体;">,</span><span class="pln" style="font-family: Arial, 宋体;"> productPrice pp
SET pp</span><span class="pun" style="font-family: Arial, 宋体;">.</span><span class="pln" style="font-family: Arial, 宋体;">price </span><span class="pun" style="font-family: Arial, 宋体;">=</span><span class="pln" style="font-family: Arial, 宋体;"> pp</span><span class="pun" style="font-family: Arial, 宋体;">.</span><span class="pln" style="font-family: Arial, 宋体;">price </span><span class="pun" style="font-family: Arial, 宋体;">*</span><span class="pln" style="font-family: Arial, 宋体;"> </span><span class="lit" style="font-family: Arial, 宋体; color: rgb(128, 0, 0);">0.8</span><span class="pln" style="font-family: Arial, 宋体;">
WHERE p</span><span class="pun" style="font-family: Arial, 宋体;">.</span><span class="pln" style="font-family: Arial, 宋体;">productId </span><span class="pun" style="font-family: Arial, 宋体;">=</span><span class="pln" style="font-family: Arial, 宋体;"> pp</span><span class="pun" style="font-family: Arial, 宋体;">.</span><span class="pln" style="font-family: Arial, 宋体;">productId
AND p</span><span class="pun" style="font-family: Arial, 宋体;">.</span><span class="pln" style="font-family: Arial, 宋体;">dateCreated </span><span class="pun" style="font-family: Arial, 宋体;"><</span><span class="pln" style="font-family: Arial, 宋体;"> </span><span class="str" style="font-family: Arial, 宋体; color: rgb(128, 0, 0);">'2004-01-01'</span>

另外一种方法是使用inner join然后更新：

<span class="pln" style="font-family: Arial, 宋体;">UPDATE product p
INNER JOIN productPrice pp
ON p</span><span class="pun" style="font-family: Arial, 宋体;">.</span><span class="pln" style="font-family: Arial, 宋体;">productId </span><span class="pun" style="font-family: Arial, 宋体;">=</span><span class="pln" style="font-family: Arial, 宋体;"> pp</span><span class="pun" style="font-family: Arial, 宋体;">.</span><span class="pln" style="font-family: Arial, 宋体;">productId
SET pp</span><span class="pun" style="font-family: Arial, 宋体;">.</span><span class="pln" style="font-family: Arial, 宋体;">price </span><span class="pun" style="font-family: Arial, 宋体;">=</span><span class="pln" style="font-family: Arial, 宋体;"> pp</span><span class="pun" style="font-family: Arial, 宋体;">.</span><span class="pln" style="font-family: Arial, 宋体;">price </span><span class="pun" style="font-family: Arial, 宋体;">*</span><span class="pln" style="font-family: Arial, 宋体;"> </span><span class="lit" style="font-family: Arial, 宋体; color: rgb(128, 0, 0);">0.8</span><span class="pln" style="font-family: Arial, 宋体;">
WHERE p</span><span class="pun" style="font-family: Arial, 宋体;">.</span><span class="pln" style="font-family: Arial, 宋体;">dateCreated </span><span class="pun" style="font-family: Arial, 宋体;"><</span><span class="pln" style="font-family: Arial, 宋体;"> </span><span class="str" style="font-family: Arial, 宋体; color: rgb(128, 0, 0);">'2004-01-01'</span>

另外我们也可以使用left outer join来做多表update，比方说如果ProductPrice表中没有产品价格记录的话，将Product表的isDeleted字段置为1，如下sql语句：

<span class="pln" style="font-family: Arial, 宋体;">UPDATE product p
LEFT JOIN productPrice pp
ON p</span><span class="pun" style="font-family: Arial, 宋体;">.</span><span class="pln" style="font-family: Arial, 宋体;">productId </span><span class="pun" style="font-family: Arial, 宋体;">=</span><span class="pln" style="font-family: Arial, 宋体;"> pp</span><span class="pun" style="font-family: Arial, 宋体;">.</span><span class="pln" style="font-family: Arial, 宋体;">productId
SET p</span><span class="pun" style="font-family: Arial, 宋体;">.</span><span class="pln" style="font-family: Arial, 宋体;">deleted </span><span class="pun" style="font-family: Arial, 宋体;">=</span><span class="pln" style="font-family: Arial, 宋体;"> </span><span class="lit" style="font-family: Arial, 宋体; color: rgb(128, 0, 0);">1</span><span class="pln" style="font-family: Arial, 宋体;">
WHERE pp</span><span class="pun" style="font-family: Arial, 宋体;">.</span><span class="pln" style="font-family: Arial, 宋体;">productId IS </span><span class="kwd" style="font-family: Arial, 宋体; color: rgb(0, 0, 139);">null</span>

另外，上面的几个例子都是两张表之间做关联，但是只更新一张表中的记录，其实是可以同时更新两张表的，如下sql：

<span class="pln" style="font-family: Arial, 宋体;">UPDATE product p
INNER JOIN productPrice pp
ON p</span><span class="pun" style="font-family: Arial, 宋体;">.</span><span class="pln" style="font-family: Arial, 宋体;">productId </span><span class="pun" style="font-family: Arial, 宋体;">=</span><span class="pln" style="font-family: Arial, 宋体;"> pp</span><span class="pun" style="font-family: Arial, 宋体;">.</span><span class="pln" style="font-family: Arial, 宋体;">productId
SET pp</span><span class="pun" style="font-family: Arial, 宋体;">.</span><span class="pln" style="font-family: Arial, 宋体;">price </span><span class="pun" style="font-family: Arial, 宋体;">=</span><span class="pln" style="font-family: Arial, 宋体;"> pp</span><span class="pun" style="font-family: Arial, 宋体;">.</span><span class="pln" style="font-family: Arial, 宋体;">price </span><span class="pun" style="font-family: Arial, 宋体;">*</span><span class="pln" style="font-family: Arial, 宋体;"> </span><span class="lit" style="font-family: Arial, 宋体; color: rgb(128, 0, 0);">0.8</span><span class="pun" style="font-family: Arial, 宋体;">,</span><span class="pln" style="font-family: Arial, 宋体;">
p</span><span class="pun" style="font-family: Arial, 宋体;">.</span><span class="pln" style="font-family: Arial, 宋体;">dateUpdate </span><span class="pun" style="font-family: Arial, 宋体;">=</span><span class="pln" style="font-family: Arial, 宋体;"> CURDATE</span><span class="pun" style="font-family: Arial, 宋体;">()</span><span class="pln" style="font-family: Arial, 宋体;">
WHERE p</span><span class="pun" style="font-family: Arial, 宋体;">.</span><span class="pln" style="font-family: Arial, 宋体;">dateCreated </span><span class="pun" style="font-family: Arial, 宋体;"><</span><span class="pln" style="font-family: Arial, 宋体;"> </span><span class="str" style="font-family: Arial, 宋体; color: rgb(128, 0, 0);">'2004-01-01'</span>

两张表做关联，更新了ProductPrice表的price字段

参考 http://blog.youkuaiyun.com/yukaizhao/article/details/8280257