CODEWITHSUNDEEP
mysqlinsert-update
JChao
JChao

Reputation: 2319

UPDATE/INSERT based on non-unique keys?

I have a table that contains

id | user | date | data1 | data2 ......

where id is the primary unique key.

I'm trying to write a query that can UPDATE if both user and date exist while INSERT if either one of them doesn't exist

I thought about the INSERT INTO...ON DUPLICATE KEY...UPDATE method, but that requires using the unique key, which I do have but not using.

What would be a good way to deal with this issue?

Upvotes: 0

Views: 300

Answers (2)

cyberbit
cyberbit

Reputation: 1365

Per discussion in comments, you should make (user, date) a unique key.

This will trigger the INSERT INTO ... ON DUPLICATE KEY UPDATE query as expected, updating rows with matching user and date fields, and inserting new ones where no match is found.

Upvotes: 2

Tudor Constantin
Tudor Constantin

Reputation: 26861

The only valid option is to implement this UPSERT in the programming language that you use with mysql, because MySQL needs a unique key for both INSERT ... INTO and REPLACE.

Or to add a unique index on the user and date columns which seems to be in concordance with your business logic anyway.

Upvotes: 0

Related Questions