如何在更新查询上创建条件

Question

i made a query which which have a condition inside update.

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'

this is the queries i test to get the right result but what happen is it is not getting the result that i wanted to get..

my problem lies on this field

    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'),

instead of getting the result accepted or pending what i get was 0.

Answer 1

you only need this:

transaction_status = CASE WHEN amount_remaining = 0 THEN 'accepted' ELSE 'pending' END

that is, you do not have to repeat "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'

Personally I prefer to use case expressions and I would not like to encourage use of IF() and CASE in the same query, which I think is just confusing.