MySQL Insert or Update (Where 2 columns match)

Question

I have a table structure like below (like dummy data below).

RecordId | UserId | TestId | Score
----------------------------------
   1     |   1    |    4   |  98
   2     |   1    |    5   |  92
   3     |   1    |    6   |  91
   4     |   2    |    4   |  99
   5     |   2    |    5   |  07
   6     |   2    |    6   |  08

I want to update the above but I don't have the RecordId handy. So lets say UserId 2 on TestId 5 got a Score 55.

We don't currently know wether a record even exists for UserId 2 on TestId 5 so it needs to add the data if it doesn't already exist. If The UserId and TestId do already exist I need to update them.

I don't 'think' i want to use any of the replace queries as I have read that these delete the old record and a create new one, which would have a new Id.

I 'think' it needs to be update with a on duplicate update but i cannot get this to work?

Any help would be much appreciated.

Show your attempt.http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html — Mihai, Jun 22 '14 at 18:20
Have read that page. Was trying this which look correct from what I read. `UPDATE test SET Score=55 WHERE UserId=2 AND TestId=5 LIMIT 1;` — Matt The Ninja, Jun 22 '14 at 18:27
Read that link again,there is a specific syntax, doesnt start with update. — Mihai, Jun 22 '14 at 18:31
This may also work: http://dev.mysql.com/doc/refman/5.0/en/replace.html — Denis V, Jun 22 '14 at 21:58
possible duplicate of [How do I UPDATE a row in a table or INSERT it if it doesn't exist?](http://stackoverflow.com/questions/690632/how-do-i-update-a-row-in-a-table-or-insert-it-if-it-doesnt-exist) — Ben, Jun 22 '14 at 22:12

score 3 · Answer 1 · answered Jun 27 '14 at 00:07

Ok so here is the answer. Turns out my query was fine but i needed to create a unique key

Here is the prepared SQL statement I used.

INSERT INTO test (UserId, TestId, Score) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE UserId=? TestId=? Score=?;

To make this update when unique combination of UserId & TestId I used the follow SQL query.

ALTER TABLE test ADD UNIQUE KEY `UserTests` (`UserId`, `TestId`);

I hope this & my explanation helps someone out.

MySQL Insert or Update (Where 2 columns match)

1 Answers1