Mysql – UPDATE SET values ignoring set ordering

MySQLupdate

I'm trying to update 2 rows at once, I want to:

1) Set coupon.start (datetime) to the original account.vipExpires (datetime)

2) Add coupon.days (int) to account.vipExpires (datetime)

3) Set coupon.end (datetime) to the new value of account.vipExpires after coupon.days have been added

UPDATE accounts a, coupons c
SET c.start = a.vipExpires,
    a.vipExpires =  DATE_ADD(a.vipExpires, INTERVAL c.days DAY),
    c.end = a.vipExpires
WHERE a.id = 1 AND c.id = 1;

The result I'm getting is:

c.start, a.vipExpires, c.end ALL end up as the new datetime with the added days.

How do I make c.start be equal to the original a.vipExpires value?

I originally thought the SET operations would execute in the order specified, it seems I was wrong.

Best Answer

Test

UPDATE coupons c, accounts a
SET c.start = a.vipExpires,
    c.end = DATE_ADD(a.vipExpires, INTERVAL c.days DAY),
    a.vipExpires = c.end
WHERE a.id = 1 AND c.id = 1;
Related Question