What is the purpose of the
REPLACE INTO command in MYSQL?
REPLACE INTO tabela(col1, col2) values(value1, value2) WHERE id = 1
REPLACE INTO is interesting.
Let's imagine a table:
ID NOME 1 Diego 2 José
I want to update José's name to José Pedro.
REPLACE INTO USUARIOS (ID, NOME) VALUES (2, 'José Pedro')
What will the command do?
I informed the name of the fields in the first couple of parentheses, ID and NAME. And in
VALUES I put the ID 2 that refers to José and in the NAME field the new value I want to update.
The command will update the record if it exists, if the ID 1 exists. If the ID value is set to
'' a new record will be added .
In other words: if the record exists, update , if not insert .
REPLACE INTO USUARIOS (ID, NOME) VALUES (NULL, 'Bigown')
Would be like this:
ID NOME 1 Diego 2 José Pedro 3 Bigown
1 – It is not necessary to use the
WHERE if the
ID is defined.
2 – The ID must be
3 – If the table has more fields and they are not defined in
VALUES , the values will be deleted. Unfortunately this is how it is.
4 – You can work around what happens in point 3 with a
SELECT on the same table. Then yes it is necessary the
REPLACE INTO USUARIOS(ID, NOME, TELEFONE, ENDERECO) SELECT 2, 'José Pedro dos Santos', TELEFONE WHERE ID = 2