MySQL: INSERT or UPDATE if exists, but not based on key column

Question

What I have is a table of completed training. Each user has a username. Each user may completed numerous courses.

The table has the following headers:

+-------------------------+----------+---------+---------+---------+---------+-----------+
| recordnumber (KEY - AI) | username |  type   | course  | status  | started | completed |
+-------------------------+----------+---------+---------+---------+---------+-----------+
| int                     | varchar  | varchar | varchar | varchar | date    | date      |
+-------------------------+----------+---------+---------+---------+---------+-----------+

And I have a PHP script set up to populate the db from a CSV upload.

What I'm trying to achieve is for it to add new rows, and to update existing ones.

The problem is that recordnumber (they key, unique field) is not constant. So instead of doing a "ON DUPLICATE KEY" query, I want to do it based on whether username and course already exist as a row.

Basically to say "If this username already has this course, update the other fields. If the username does not have this course, add this as a new row".

The query that I have at the moment (which works based on key) is:

INSERT into table(recordnumber, username,type,course,status,started,completed) values('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$data[5]','$data[6]')

ON DUPLICATE KEY UPDATE username='$data[1]',type='$data[2]',course='$data[3]',status='$data[4]',started='$data[5]',completed='$data[6]'

Any thoughts on how I could amend the query to get it to check based on username and course instead of duplicate key?

Thank you. :-)

Answer 1

The most correct way would be to create a unique index on username - course columns and use on duplicate key update.

Obviously, you can issue a select before the insert checking for existing record with same user name and course and issue an insert or an update as appropriate.