MySQL Query Question

[Rozzy85]

My MySQL skills are a little rusty and I trying to figure out how to make a statement that will add a value to an existing field value in the table. I have a table that contains 3 fields id, name and score. For example:-

id name score
1 John 5
2 Alex 2
3 Sarah 7

It's crude I know.

So I add a new entry (Stacey, 4) and that gets a assigned an ID and added. Now I want to amend an existing score, say Alex's by +3. Can I make a statement to add the scores together if it finds an ID that aleady exists in the field?

 

[rpstewart]

Not quite sure I follow the question. Do you want to do a simple update of the score or something a bit fancier that updates the score if the user exists but inserts a new row if the user doesn't exist?

 

[Rozzy85]

something a bit fancier that updates the score if the user exists but inserts a new row if the user doesn't exist?

Essentially this.

 

[Ciphon]

So I add a new entry (Stacey, 4) and that gets a assigned an ID and added. Now I want to amend an existing score, say Alex's by +3. Can I make a statement to add the scores together if it finds an ID that aleady exists in the field?

So something like this...

Update TABLE
Set Score = Score + 3
Where ID = 2

?

 

[Ciphon]

Essentially this.

Ahh.. Possibly a MERGE then.. ?

Not sure how these are done in MySQL..

 

[Rozzy85]

I'm sure I'm going to need to use an IF statement although I'm not sure how to go about writing the condition to check if the user ID already exists in the specified field.

 

[rpstewart]

Take a look at the ON DUPLICATE KEY UPDATE addition to the INSERT command.

 

[Rozzy85]

That worked, thanks.