MySQL change a value depending on its original value

Hy everyone, Long time reader, first time poster.

This sounds like it should be really simple but I can't find the solution anywh开发者_JAVA技巧ere. I'm building a ratings system where people can rate if something is active or not. It has its own little logic but for it to work I need to.

1. check the items rating
2. depending on the current rating change it to a pre set amount.

I could hard code it in PHP with two SQL statements but I'm sure using a single stored procedure (one for vote up, another for vote down) will be much faster.

example table:

item_id | item_rating
---------------------
   10   |    1

logic to vote item_rating up:

if | then
---------
 0 |  1
 1 |  2
-1 |  1
-2 |  1
 2 |  2

logic to vote item_rating down:

if | then
---------
 0 | -1
 1 | -1
-1 | -2
-2 | -2
 2 | -1

I know a simple points based system would be easier but due to the nature of the system this is the simplest solution I could find.

Can someone explain how I would use IF statements in SQL to achieve this? I'm sure the answer is really obvious to someone in the know.

(btw using the latest version of MySQL)

Is this what you're looking for? Here's an upvote:

UPDATE rating
SET item_rating = IF(item_rating < 1, 1, 2);

Here's a downvote:

UPDATE rating
SET item_rating = IF(item_rating > -1, -1, -2);

This is untested, but I think it should work.

update items i
set item_rating = (select i.item_rating + `then` from item_rating
                   where `if` = i.item_rating)
where i.item_id = 10