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?
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?
Try to use:
INSERT ... ON DUPLICATE KEY UPDATE
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 14.2.5.3 INSERT ... ON DUPLICATE KEY UPDATE Syntax from MySql.
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.
Greetings
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.