MySql中以其他表作为更新表条件

场景描述

紧接着上一篇文章中提到的问题，项目中需要修改大批量的用户状态。如果碰到数据量超出的PHP所能存贮的数据量，那么之前使用PHP Generator的方法也是无济于事的，唯一可行的办法就是直接使用SQL去执行。开始的想法也是使用SQL语句，但是失败了，再来看看失败错误的原因在哪里。

写法一

1
2
3

UPDATE (SELECT * FROM products AS p JOIN users AS u ON u.id=p.uid WHERE u.status=1) AS a 
SET a.is_apply=2,a.send=3 
WHERE a.state=0 AND a.rec=1;

思路是把满足条件的数据全部查出来，再设置一个别名去修改。但返回的结果是：

1	The target table a of the UPDATE is not updatable

没有table a，所以无法更新。

写法二

UPDATE products 
SET is_apply=2,send=3 
WHERE state=0 AND rec=1 AND pid IN 
(SELECT pid FROM products AS p JOIN users AS u ON u.id=p.uid WHERE u.status=1);

查出了满足条件的pid，设置再where条件中，但是返回的结果是：

1	You can't specify target table 'products' for update in FROM clause

中文翻译：您不能在FROM子句中指定更新的目标表“产品”。大概意思products表就要更新了，所以不要再查它了╮(╯▽╰)╭
不让我查products表，但我一定要查呀，那怎么办？

写法三

UPDATE products 
SET is_apply=2,send=3 
WHERE state=0 AND rec=1 AND pid IN 
(SELECT pid FROM (SELECT tmp.* FROM products tmp) AS p
  JOIN users AS u on u.id=p.uid WHERE u.status=1);

把products存在一个临时表里，这样名字不冲突了，应该不会报错了吧。返回结果：

1	11478 rows affected in 2s 239ms

成功！
虽然处理时间稍微长了一点，但肯定比循环遍历来的更有效率。(^o^)/~