我做了一个查询,里面有一个条件更新。
UPDATE tbl_delivery SET amount_paid = 700000, amount_remaining = CASE WHEN 50000 < 700000 THEN 0 ELSE 50000 - 700000 END, transaction_status = IF(amount_remaining = 0, transaction_status = 'accepted', transaction_status='pending'), payment_status = CASE WHEN amount_remaining = 0 THEN payment_status = 'paid' ELSE payment_status = 'unpaid' END, modified = '2017-12-05 12:14:02' WHERE id = '1' AND user_id = '201720000003'
UPDATE tbl_delivery SET amount_paid = 700000, amount_remaining = CASE WHEN 50000 < 700000 THEN 0 ELSE 50000 - 700000 END, transaction_status = CASE WHEN amount_remaining = 0 THEN transaction_status = 'accepted' ELSE transaction_status = 'pending' END, payment_status = CASE WHEN amount_remaining = 0 THEN payment_status = 'paid' ELSE payment_status = 'unpaid' END, modified = '2017-12-05 12:01:17' WHERE id = '1' AND user_id = '201720000003'
这是我测试以获得正确结果的查询,但是发生的是没有得到我想要获得的结果。
我的问题在于这个领域
transaction_status = CASE WHEN amount_remaining = 0 THEN transaction_status = 'accepted' ELSE transaction_status = 'pending' END,
transaction_status = IF(amount_remaining = 0, transaction_status = 'accepted', transaction_status='pending'),
而不是得到结果
accepted
或pending
我得到的是0。 最佳答案
您只需要这个:
transaction_status = CASE WHEN amount_remaining = 0 THEN 'accepted' ELSE 'pending' END
也就是说,您不必重复“ transaction_status =“
UPDATE tbl_delivery
SET
amount_paid = 700000
, amount_remaining = CASE WHEN 50000 < 700000 THEN 0 ELSE 50000 - 700000 END
, transaction_status = IF(amount_remaining = 0, 'accepted','pending')
, payment_status = CASE WHEN amount_remaining = 0 THEN 'paid' ELSE 'unpaid' END
, modified = '2017-12-05 12:14:02'
WHERE id = '1'
AND user_id = '201720000003'
就我个人而言,我更喜欢使用
case expressions
,并且我不鼓励在同一查询中使用IF()和CASE,我认为这很令人困惑。