IF statement in Sql query with multiple fields

Question

What I'm trying to accomplish is this - update the table with certain values only IF the high-score is better than the current one.

Got table with columns userId, highScore, something, dateLastPlayed

User with userId of 1 has beaten his own score, and in that case I want to update all the fields in the row. If he has not beaten his own high-score, I just want to update the dateLastPlayed field.

This is how I insert stuff so far

INSERT INTO table VALUES ('$userId', '$highScore', '$something', '$dateLastPlayed') ON DUPLICATE KEY UPDATE highScore='$highScore', something='$something', dateLastPlayed='$dateLastPlayed'

Note that userId has unique key.

This works fine but this updates highScore, something and dateLastPlayed fields every time. I want to update fields highScore and something only IF the variable $highScore is greater than what is current set in the database.

I have read the MySQL docs but I do not quite get it. How can I accomplish this? Note that I could do this using multiple queries and fetching data and then make more queries using that data, but I feel that is simply not the way to go and that it would take literally seconds for you guys to come up with a brilliant way to tackle the problem.

Many thanks!

check this: INSERT ... ON DUPLICATE KEY UPDATE with WHERE?http://stackoverflow.com/questions/2469457/insert-on-duplicate-key-update-with-where — mucio, Oct 11 '13 at 00:03

score 1 · Accepted Answer · answered Oct 10 '13 at 23:59

If you want to do it in one SQL, it'll be something along the lines of:

INSERT INTO table VALUES ('$userId', '$highScore', '$something', '$dateLastPlayed') 
ON DUPLICATE KEY 
UPDATE 
    highScore=
        case 
            when highScore >= $highScore then highScore
            else '$highScore'
        end,
    something=
        case 
            when highScore >= $highScore then something
            else '$something'
        end,
    dateLastPlayed ='$dateLastPlayed'

The disadvantage of this compared to multiple statements would be that the business logic is on the database / SQL side. Personally I prefer to keep business logic in the code, all in one place, for easier maintenance later.

Gonna test it out tomorrow, seems to be reasonable. Many thanks! — Toni Perić, Oct 11 '13 at 00:02

score 0 · Answer 2 · answered Oct 10 '13 at 23:59

0

Add a condition to the WHERE clause:

update mytable set
highScore = $highScore,
something = $something
where userid = $userid
and highScore < $highScore -- only update if new high score exceeds existing

answered Oct 10 '13 at 23:59

Bohemian

412,405
93
575
722

The thing is I have to use the INSERT and ON DUPLICATE KEY, not the UPDATE query itself. Thanks for the help though. – Toni Perić Oct 11 '13 at 00:01

IF statement in Sql query with multiple fields

2 Answers2