CODEWITHSUNDEEP
mysqlsql
Tim
Tim

Reputation: 807

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

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. :-)

Upvotes: 10

Views: 20612

Answers (4)

John Ruddell
John Ruddell

Reputation: 25842

create a key on the username and course column and then use on duplicate key

CREATE TABLE test (
    username varchar(255) NOT NULL,
    course varchar(255),
    num_entries INT DEFAULT 0,
    UNIQUE KEY (username, course)
);

insert into test (username, course) values
('billybob', 'math'),
('billy', 'math'),
('billybob', 'math'),
('bob', 'math')
ON DUPLICATE KEY UPDATE num_entries = num_entries + 1;

this is a simple example, but you should understand what to do from here

SAMPLE FIDDLE

so putting this to work on your table

ALTER TABLE `courses` -- assuming the table is named courses
    ADD CONSTRAINT `UK_COURSE_USERNAME` UNIQUE (username, course);

then your insert should just be the same as what you have

Upvotes: 7

Misa Lazovic
Misa Lazovic

Reputation: 2823

You might use ON DUPLICATE KEY UPDATE if you added unique constraint for username and course value pair like this:

ALTER TABLE `table` ADD CONSTRAINT `UK_table_username_course` UNIQUE (username, course);

Upvotes: 1

abalos
abalos

Reputation: 1361

Example query in reference to my comment above.

IF EXISTS(SELECT id FROM Table WHERE username = '$data[1]' AND course <> '$data[3]')
(
UPDATE username='$data[1]',type='$data[2]',course='$data[3]',status='$data[4]',started='$data[5]',completed='$data[6]'
)
(
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]')
)

Upvotes: 1

Shadow
Shadow

Reputation: 34232

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.

Upvotes: 6

Related Questions