CODEWITHSUNDEEP
sqlmysqllogic
Robert
Robert

Reputation: 13

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 anywhere. 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)

Upvotes: 1

Views: 245

Answers (2)

theazureshadow
theazureshadow

Reputation: 10049

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);

Upvotes: 3

jordanbtucker
jordanbtucker

Reputation: 6078

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

Upvotes: 0

Related Questions