There is the Insert Update in MySQL


I would like to know if there is an Insert Update instruction in MySQL, something that would know that if that record already exists in my table, update it and create a new one, and if so, how is it handled?

asked by Santiago Muñoz 19.10.2016 в 18:54

3 answers


Try to use:


An example of a query using this would be:

INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE    
name="A", age=19

This response has been translated from the response from StackOverflow in English.

For more information visit the section INSERT ... ON DUPLICATE KEY UPDATE Syntax from MySql.

answered by 19.10.2016 / 18:58

I leave you an alternative to @Joacer, which is the one of the sentence REPLACE , whose syntax is the same as insert, which in tables with Auto Increment continues the numbering, while in the way suggested by @Joacer there is a small problem with the Auto Increment that causes the number to be skipped .

on duplicate key would apply a genuine update, while REPLACE would make you DELETE and% INSERT in duplicates. (Which can give you a problem if you have the id in other tables as foreign keys)

As you can see both have their advantages and disadvantages.


answered by 19.10.2016 в 19:47

The most user-friendly resolution is to use the clause ON DUPLICATE KEY UPDATE , provided that the table you are trying to enter a row with has Primary Key . . p>

Assuming you have the Clients table with the fields: Num , Name, Phone. (Num is Primary Key)

You will insert with the following sentence:

INSERT INTO Clientes(Num, Nombre, Telefono) VALUES(1,'Agustin','123456') ON DUPLICATE KEY UPDATE Telefono = VALUES(Telefono);

The clause DUPLICATE KEY UPDATE followed by the Field = and here you can place VALUES (Phone) so that you do not have to repeat the value '123456' again.

answered by 19.10.2016 в 20:00