CODEWITHSUNDEEP
mysql
Adam
Adam

Reputation: 20882

Mysql + ON DUPLICATE KEY UPDATE

 INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=3;

Is it possible to set this so it doesn't require and entire matching row. eg: if column 'a' is a duplicate then do the update?

thx

Upvotes: 1

Views: 2087

Answers (2)

Marc B
Marc B

Reputation: 360632

ON DUPLICATE KEY does just that... if the data you're inserting violates a unique key requirement, turn it into an update on the row which has the key combination which caused the violation. If your primary key is only one of the fields (e.g. 'a'), and you already had a row in the table where a=1, then you'd get an update instead and that original row would get its 'c' set to 3.

If it's a composite key (say, 'a,b'), then if you had an existing records with a=1 and b=2, then that rows' C would get changed to 3 instead of a new record being created.

If there's no unique/primary keys on this table, then you'd never get an update, it'd just be an extra-verbose insert statement.

Upvotes: 6

goat
goat

Reputation: 31813

Whether or not a row is a duplicate is dictated by the constraints on the table. This would be your primary key, as well as all unique keys on the table. So, if you have a non multi column unique index on the a column, then it would work.

Otherwise, you could probably accomplish that with a trigger.

Upvotes: 2

Related Questions