Upsert - Update if exists else Insert in MySQL

Question

I'm looking for a simple upsert (Update/Insert). The tables looks like this

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| email | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

There is no key here. The idea is to insert if email is different, else update. The Insert on duplicate key in mysql doesn't suit the purpose.

Is there an elegant way to do this?

So id is a non-unique field? Multiple persons can have the same id? — Emil Vikström
– Emil Vikström, Commented Aug 27, 2012 at 17:13
Upsert doesn't make sense without a unique key. How do you determine if a row exists? If it matches on all fields does it already exists or does it only have to match on one or two fields? — Tim Gautier
– Tim Gautier, Commented Aug 27, 2012 at 18:49
Now that you have a unique key you can use the "on duplicate key update" syntax, correct? — Tim Gautier
– Tim Gautier, Commented Aug 27, 2012 at 19:07

Marcus Adams · Accepted Answer · 2012-08-27 18:28:39Z

3

If you cannot add a unique key on the email column, you'll check to see if the record exists first (preferably in a transaction):

SELECT id FROM mytable WHERE email = '[email protected]' FOR UPDATE

then update the record if it exists:

UPDATE mytable SET name = 'my name' WHERE id = ?

otherwise insert it:

INSERT INTO mytable (name, email) VALUES ('my name', '[email protected]')

answered Aug 27, 2012 at 18:28

Marcus Adams

54k9 gold badges98 silver badges145 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

absessive Over a year ago

is there a way to do this in a single sql statement?

TheHe · Accepted Answer · 2012-08-27 17:13:54Z

0

what about:

REPLACE INTO table VALUES(1,'hello', '[email protected]');

answered Aug 27, 2012 at 17:13

TheHe

2,97221 silver badges24 bronze badges

Collectives™ on Stack Overflow

Upsert - Update if exists else Insert in MySQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related