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?

Answer 1

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]')

Answer 2

what about:

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