Use if statement update table

Question

I am trying to use this command to update saving.balance using IF statement:

UPDATE TABLE saving s, time t
SET s.balance = IF(t.currency_TYPE = ‘RMB’, s.balance + t.balance * t.interest)
WHERE t.ID = 'input'
AND s.User = t.User;

However, MySQL gives me ERROR 1064, what's wrong and how to correct it?

You probably need the else part of the if condition. See http://stackoverflow.com/questions/9916827/mysql-if-statement — Mark Davidson, Dec 12 '12 at 11:51

score 2 · Accepted Answer · answered Dec 12 '12 at 11:53

you forgot the 3th argument in your the IF function and other syntax stuff :-)

why you make you script not with where? like this:

UPDATE saving s
INNER JOIN time t
    ON t.ID = 'input'
    AND t.User = s.User
SET s.balance = s.balance + t.balance * t.interest
WHERE t.currency_TYPE = 'RMB';

you will update just records with currency_type rmb!

OR

UPDATE saving s
INNER JOIN time t
    ON t.ID = 'input'
    AND t.User = s.User
SET s.balance = (t.currency_TYPE = 'RMB', s.balance + t.balance * t.interest, 0);

score 2 · Answer 2 · answered Dec 12 '12 at 11:55

Try this instead:

UPDATE saving s
INNER JOIN `time` t ON s.`User` = t.`User`
SET s.balance = CASE 
                  WHEN t.currency_TYPE = 'RMB' THEN s.balance + 
                                                    t.balance * t.interest
                  ELSE s.balance -- Don't forgot this, default is NULL
                END
WHERE t.ID = 'input';

Or:

UPDATE saving s
INNER JOIN `time` t ON s.`User` = t.`User`
SET s.balance = s.balance + t.balance * t.interest
WHERE t.ID = 'input'
  AND t.currency_TYPE = 'RMB' ;

score 1 · Answer 3 · answered Dec 12 '12 at 11:51

1

You need to mention the else part

UPDATE TABLE saving s, time t
SET s.balance = IF(t.currency_TYPE = ‘RMB’, s.balance + t.balance * t.interest , 0)
WHERE t.ID = 'input'
AND s.User = t.User;

answered Dec 12 '12 at 11:51

senK

2,782
1
27
38

Use if statement update table

3 Answers3