选择中的嵌套更新 [英] Nested update in select

问题描述

我正在执行

SELECT id as uid, (
    UPDATE trans SET paidout=1
        WHERE user_id=uid AND SUM(amount)>0.5234
)
FROM accounts

但是没有成功，为什么这不起作用?

But no success, why doesn't this work?

推荐答案

您可以使用子查询首先从 trans 接收所有 user_id，其中 >amount 大于 0.5234.然后使用 INNER JOIN 匹配 UPDATE trans 语句和 SETpaidout = 1 中的所有这些 user_id 值.

You can use a subquery to first receive all user_id from trans for which the sum of amount is greater than 0.5234. Then use an INNER JOIN to match all these user_id values in the UPDATE trans statement and SET paidout = 1.

无需涉及accounts 表.

UPDATE trans
    INNER JOIN (
        SELECT user_id
            FROM trans
            GROUP BY user_id
            HAVING SUM(amount) > 0.5234
    ) AS users
    ON trans.user_id = users.user_id
    SET paidout = 1;

DEMO @ SQL Fiddle

DEMO @ SQL Fiddle

这篇关于选择中的嵌套更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！