38

I have to use INSERT and UPDATE in single query. For that SQL having MERGE statement.

Is MERGE statement supported in MySQL. If supported, please provide sample.

Mike
  • 14,010
  • 29
  • 101
  • 161
Gopal
  • 787
  • 3
  • 13
  • 19
  • 1
    MySQL supports the use of INSERT ... ON DUPLICATE KEY UPDATE syntax[2] which can be used to achieve a similar effect with the limitation that the join between target and source has to be made only on PRIMARY KEY or UNIQUE constraints, which is not required in the ANSI/ISO standard. It also supports REPLACE INTO syntax,[3] which first attempts an insert, and if that fails, deletes the row, if exists, and then inserts the new one. – Mitch Wheat Mar 08 '17 at 04:34
  • 3
    Is it possible to proceed without primary key or unique key?. – Gopal Mar 08 '17 at 04:46

1 Answers1

51

MERGE is not supported by MySQL, However, there is other possible way of doing the same:

INSERT...ON DUPLICATE KEY UPDATE

If you specify the ON DUPLICATE KEY UPDATE option in the INSERT statement and the new row causes a duplicate value in the UNIQUE or PRIMARY KEY index, MySQL performs an update to the old row based on the new values.

Mehmood Memon
  • 1,129
  • 2
  • 12
  • 20
  • Thanks for your reply. Is it possible to add where condition in that statement.Please provide sample with WHERE condition. – Gopal Mar 08 '17 at 04:52
  • No, you can't use WHERE condition in the update portion of the query in this case. Alternatively, you can use IF statements. – Mehmood Memon Mar 08 '17 at 06:18
  • 6
    Worth noting that this doesnt do everything that merge does - for example if you have a record that should be removed, there is no `WHEN NOT MATCHED BY TARGET DELETE;` – Shaun Rowan Nov 10 '18 at 19:29
  • If you know the exact answer, please can you make a proper answer that shows the full command? – user9371654 Apr 24 '19 at 18:51
  • The way this is written seems to imply the duplication of values after the `UPDATE` keyword is not required. – beppe9000 Mar 23 '20 at 12:41