mysql - 操作数应包含 1 列 - 使用 Case 语句更新和选择

Question

几个小时以来，我一直在努力克服这个错误，但没有任何运气。

  UPDATE `users` t1

SET t1.`Earnings`= (SELECT SUM(ROUND(t2.`vCost`,2)) AS totalEarned,
(
CASE 
   WHEN t2.`vPayment` = 'CreditCard' THEN t1.`Earnings` = (t1.`Earnings` * 1.05)
   WHEN t2.`vPayment` = 'Cash' THEN t1.`Earnings` = (t1.`Earnings` * 1.25)
END
) AS total

FROM `transactions` t2 WHERE t2.`ID` = t1.`ID` AND t2.payout != 'PAIDOUT')

当前结构:

:::Transactions Table:::
ID,
UserID,
Cost

:::User Table:::
ID,
UserID,
Earnings

结果:

Operand should contain 1 column(s)

当前代码:

 UPDATE `users` u 
SET u.currentPayout = (SELECT SUM(ROUND(s.vCost,2)) AS totalEarned s
                       FROM transactions s
                       WHERE s.USERID = u.USERID AND s.payout != 'PAIDOUT'

当前代码计算并成功保存，但是，我想将 vPayment 实现为查询中的一个因素。

因此，如果 vPayment 等于 Cash，则将其乘以 1.25。

Answer 1

这就是您要找的(编辑)吗？

UPDATE `users` u 
SET u.`Earnings` = (SELECT SUM(ROUND(s.vCost * IF(`vPayment` = 'Cash', 1.25, 1.05), 2)) 
                    FROM transactions s
                    WHERE s.USERID = u.USERID AND s.payout != 'PAIDOUT'