mysql 多表 update sql语句总结

mysql 多表 update 有几种不同的写法。

假定我们有两张表，一张表为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：