mysql - 基于带有子选择的 where 语句更新 mysql 中的列

Question

我有这个伪 SQL 代码来实现我想要实现的目标:

UPDATE orders o
SET o.datePaid = null

WHERE
    (
        SELECT SUM(amount)
        FROM transactions t
        WHERE t.orderId = o.id
        AND t.status = 'success'
        AND t.type = 'refund'
    )
    >=
    (
        SELECT SUM(amount)
        FROM transactions t
        WHERE t.orderId = o.id
        AND t.status = 'success'
        AND t.type IN ('purchase', 'capture')
    )

我如何在 SQL 中执行此操作？

Answer 1

我觉得你的方法很有趣。这是一个更简洁的方法:

UPDATE orders o
    SET o.datePaid = null
WHERE (SELECT SUM(CASE WHEN t.type = 'refund' THEN amount
                       WHEN t.type IN ('purchase', 'capture') THEN -amount
                  END)
       FROM transactions t
       WHERE t.orderId = o.id AND
             t.status = 'success'
      ) > 0;