[SOLVED] IF statements in MySql UPDATE

[Beauford]

I posted this in the MySQL group, but it might be relevent here as well. If not, I apologize for the post.

 

I am trying to add interest to a dollar value, but I don't want the interest to go higher than a certain number.

 

Example

 

(update table set SET bankmoney = IF (bankmoney * .02 < 1000, bankmoney=bankmoney * .02 else bankmoney = bankmoney + 1000);

 

Of course the above doesn't work, The question is, how do I get it to work.

 

Thanks. I only have one gray hair left and I'd like to keep it.

 

B

[corbin]

I would just do it in 2 queries....

[Beauford]

Can you elaborate. I am going to be checking a DB with 30000+ entries and each one has to be checked.

 

Thanks.

[corbin]

UPDATE table SET bankmoney = bankmoney*.02 WHERE bankmoney < 50000;

 

 

UPDATE table SET bankmoney = bankmoney+1000 WHERE bankmoney >= 50000;

[fenway]

Or with a CASE statement:

 

UPDATE yourTable SET bankmoney = CASE WHEN bankmoney >= 50000 THEN bankmoney*.02 ELSE bankmoney+1000 END

 

Assuming NULL isn't an option.

[Beauford]

That's not what I need. I need to find out what the interest is first, and if it is greater than a certain value, only update it by that value.

 

So if the interest is 1000 and the ceiling is 500, then I want to update bankmoney by 500.

 

Thanks

[fenway]

well, then what you originally posted should work, with minor syntactical changes:

 

update table set SET bankmoney = IF (bankmoney * .02 < 1000, bankmoney * .02, bankmoney + 1000)

 

(untested -- backup your table first)

 

 

[Beauford]

I actually figured it out using a where statement and it works perfectly. I just had to use two statements.

 

 

[fenway]

I actually figured it out using a where statement and it works perfectly. I just had to use two statements.

 

 

Then post those two.

[Beauford]

I actually figured it out using a where statement and it works perfectly. I just had to use two statements.

 

 

Then post those two.

 

$db->query("UPDATE users set bankmoney = bankmoney+bankmoney * .02 where (bankmoney > 0 and bankmoney * .02 < 300000) and donatordays = 0");

$db->query("UPDATE users set bankmoney = bankmoney+300000 where (bankmoney > 0 and bankmoney * .02 > 300000) and donatordays = 0");