Insert and if it already exists update the columns SQL

Question

INSERT INTO users (firstname, lastname, email, mobile) VALUES ('Karem', 'Parem', '[email protected]', '123456789');

This what i would like to do, but if a row with the same email [email protected] already exists, then it should update that row with these values.

I found the http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html but I dont understand it

What specifically don't you understand? Have you tried it? What happened? What do you have so far? — Mark Byers, Oct 25 '11 at 11:19
possible duplicate of [How do I update if exists, insert if not (aka upsert or merge) in MySQL?](http://stackoverflow.com/questions/1218905/how-do-i-update-if-exists-insert-if-not-aka-upsert-or-merge-in-mysql) — Erwin Brandstetter, Oct 25 '11 at 11:35

Mark Byers · Accepted Answer · 2011-10-25T16:28:40.943

5

Use ALTER TABLE to add a unique constraint on the email column then try this query:

INSERT INTO users(firstname, lastname, email, mobile)
VALUES ('Karem', 'Parem', '[email protected]', '123456789')
ON DUPLICATE KEY UPDATE
    firstname = 'Karem',
    lastname = 'Parem',
    email = '[email protected]', -- you can probably omit this
    mobile = '123456789'

Be careful though - if you have any other unique keys defined on your table (including the primary key, which is by definition unique) then they can also trigger the update.

edited Oct 25 '11 at 16:28

answered Oct 25 '11 at 11:21

Mark Byers

811,555
193
1,581
1,452

How do i say that its the email that should look for unique? The email has a unique index with a indexname "uniq_email" – Karem Oct 25 '11 at 11:24
I dont get it. First you say use alter table to make it unique and now you say that i cant check for email anymore because its unique index? – Karem Oct 25 '11 at 13:17
@Karem: How many unique indexes do you have on your table? If you only have one then there is no problem. – Mark Byers Oct 25 '11 at 13:29

score 1 · Answer 2 · answered Oct 25 '11 at 11:20

You achieve this goal by using triggers but IMHO checking the existence of the primary key before inserting/updating is more appropriate as:

triggers may lead to concurrency issue, the 'insert or update' can use a transaction to shield from such issues
your code will be more readable
it will be more efficient

Insert and if it already exists update the columns SQL

2 Answers2