MySQL - 组合 2 个具有不同 SET 和 WHERE 子句的查询

发布于 2024-11-27 15:40:16 字数 602 浏览 2 评论 0原文

不久前@Richard Harris 给出了一个很好的答案对于类似的问题，但我相信我的情况略有不同。

正如您所看到的，我有 2 个连续的 UPDATE 语句针对相同的表和字段，但具有不同的 SET 和 WHERE 子句。

$this->db->query("
UPDATE user_profiles
SET    reputation = reputation + 15
WHERE  user_id = $answer_author_id;
");

$this->db->query("
UPDATE user_profiles
SET    reputation = reputation + 2
WHERE  user_id = $user_id;
");

我想知道是否可以将其合并为单个查询，或者是否需要进一步规范化。另外，这些连续查询的效率是否太低了？如果没有，我就不会费心尝试合并到单个查询中。

非常感谢您对此的想法。

原文

A while ago @Richard Harris gave a great answer for a similar question, but I believe my situation is slightly different.

As you can see I have 2 consecutive UPDATE statements targeting the same table and fields but with different SET and WHERE clauses.

$this->db->query("
UPDATE user_profiles
SET    reputation = reputation + 15
WHERE  user_id = $answer_author_id;
");

$this->db->query("
UPDATE user_profiles
SET    reputation = reputation + 2
WHERE  user_id = $user_id;
");

I wonder if this can be combined into a single query, or if this requires further normalization. Also, are these consecutive queries too inefficient? If not, I wouldn't bother trying to combine into a single query.

Your thoughts on this are much appreciated.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

寒尘 2024-12-04 15:40:16

你可以这样做：

UPDATE user_profiles
SET    reputation = reputation + 
           case when user_id = $answer_author_id then 15
                when user_id = $user_id then 2
                else 0
           end
WHERE  user_id = $answer_author_id or user_id = $user_id;

you can do this:

UPDATE user_profiles
SET    reputation = reputation + 
           case when user_id = $answer_author_id then 15
                when user_id = $user_id then 2
                else 0
           end
WHERE  user_id = $answer_author_id or user_id = $user_id;

回复收藏 0 原文

风向决定发型 2024-12-04 15:40:16

该解决方案实际上与这个，只是有点嵌套。

UPDATE my_table SET reputation = 
    IF( USER_ID = $user_id, REPUTATION + 2, REPUTATION + 15 ) 
        WHERE USER_ID IN ( $user_id, $answer_author_id );

The solution actually isn't terribly different from this one, just a tad more nested.

UPDATE my_table SET reputation = 
    IF( USER_ID = $user_id, REPUTATION + 2, REPUTATION + 15 ) 
        WHERE USER_ID IN ( $user_id, $answer_author_id );

回复收藏 0 原文