2
INSERT INTO users (firstname, lastname, email, mobile) VALUES ('Karem', 'Parem', 'mail@mail.com', '123456789');

This what i would like to do, but if a row with the same email mail@mail.com 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

Karem
  • 17,615
  • 72
  • 178
  • 278
  • 1
    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

2 Answers2

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', 'mail@mail.com', '123456789')
ON DUPLICATE KEY UPDATE
    firstname = 'Karem',
    lastname = 'Parem',
    email = 'mail@mail.com', -- 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.

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
1

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
vc 74
  • 37,131
  • 7
  • 73
  • 89