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.

Answer 1

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.