mysql - 在 UPDATE 语句中使用 WHERE 子句

Question

我有一个像这样的 update 语句:

update user_stats set
    requestsRecd = (select count(*) from requests where requestedUserId = 1) where userId = 1,
    requestsSent = (select count(*) from requests where requesterUserId = 2) where userId = 2;

我想做的是更新同一张表，但该表中的不同用户，包括一个用户收到的好友请求计数，以及另一个用户发送的好友请求计数。

如果我删除 where 子句，我正在做的事情会起作用，但是，这会更新整个表中的所有用户。

知道如何使用其中的 where 子句做类似的事情或使用其他方法获得相同的结果吗？

Answer 1

(正如其他几个答案中所建议的那样，显然，您可以运行两个单独的语句，但要回答您提出的问题，是否可能以及如何做......)

是的，可以用一条语句完成更新操作。您需要将条件测试作为语句的一部分(例如示例的 WHERE 子句中的条件，但这些条件不能进入 UPDATE 语句的 WHERE 子句。

我们在一个 UPDATE 语句中执行此操作的最大限制是该语句必须为两个行的列两个分配一个值。 p>

我们可以使用的一个“技巧”是将列的当前值分配回列，例如

 UPDATE mytable SET mycol = mycol WHERE ...

这不会导致列中存储的内容发生变化。 (仍然会在满足 WHERE 子句的行上触发 BEFORE/AFTER 更新触发器，但当前存储在列中的值 不会被改变。)

因此，我们无法有条件地指定要根据哪些行更新哪些列，但我们可以在我们分配给的表达式中包含一个条件专栏。例如，考虑:

 UPDATE mytable SET mycol = IF(foo=1, 'bar', mycol)

对于 foo=1 计算结果为 TRUE 的行，我们会将“bar”分配给该列。对于所有其他行，列的值将保持不变。

在您的情况下，您希望在特定条件为真时为列分配一个"new"值，否则保持不变。

考虑这个语句的结果:

UPDATE user_stats t
  SET t.requestsRecd = IF(t.userId=1, expr1, t.reqestsRecd) 
    , t.requestsSent = IF(t.userId=2, expr2, t.reqestsSent) 
WHERE t.userId IN (1,2);

(我省略了返回您要分配的计数值的子查询，并将其替换为“expr1”和“expr2”占位符。这只会让您更容易看到模式，而不会使其变得困惑隐藏模式的语法。)

您可以将上述语句中的 expr1 和 expr2 替换为返回计数的原始子查询。

---

作为替代形式，也可以在内联 View (此处别名为 v)中使用单行返回这些计数，然后指定连接操作。像这样:

UPDATE user_stats t
 CROSS
  JOIN ( SELECT (select count(*) from requests where requestedUserId = 1) AS c1
              , (select count(*) from requests where requesterUserId = 2) AS c2
       ) v
   SET t.requestsRecd = IF(t.userId=1, v.c1 ,t.reqestsRecd) 
     , t.requestsSent = IF(t.userId=2, v.c2 ,t.reqestsSent)
 WHERE t.userId IN (1,2)

由于内联 View 返回单行，我们不需要在 WHERE 子句中使用任何 ON 子句或谓词。 (*我通常在这里包含 CROSS 关键字，但它可以省略而不影响语句。我包含 CROSS 关键字的主要理由是让 future 的读者清楚地知道意图，他们可能会因省略连接谓词而感到困惑，希望在 ON 或 WHERE 子句中找到一些。CROSS 关键字提醒读者有意省略连接谓词。)

另请注意，即使我们省略了 WHERE 子句中的谓词，该语句的效果也是一样的，我们可以遍历整个表中的所有行，并且只有 userId=1 或 userId=2 的行会受到影响. (但我们想包括 WHERE 子句，以提高性能；我们没有理由获取我们不想修改的行的锁。)

因此，总结一下:是的，可以在单个语句中执行两行(或更多行)的条件更新。至于是使用这种形式，还是使用两个单独的语句，由您决定。