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?stackoverflow.com/questions/2469457/… — mucio
– mucio, Commented Oct 11, 2013 at 0:03

JackDev · Accepted Answer · 2013-10-10 23:59:53Z

1

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.

answered Oct 10, 2013 at 23:59

JackDev

11.2k12 gold badges53 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Toni Perić Over a year ago

Gonna test it out tomorrow, seems to be reasonable. Many thanks!

Bohemian · Accepted Answer · 2013-10-10 23:59:37Z

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, 2013 at 23:59

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

1 Comment

Toni Perić Over a year ago

The thing is I have to use the INSERT and ON DUPLICATE KEY, not the UPDATE query itself. Thanks for the help though.

Collectives™ on Stack Overflow

IF statement in Sql query with multiple fields

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related