What is REPLACE INTO for in MYSQL?

Question:

What is the purpose of the REPLACE INTO command in MYSQL?

Example:

REPLACE INTO tabela(col1, col2) values(value1, value2) WHERE id = 1

Answer:

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 NULL or '' 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

Comments

1 – It is not necessary to use the WHERE if the ID is defined.

2 – The ID must be auto_increment .

3 – If the table has more fields and they are not defined in INTO and 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 WHERE in SELECT .

REPLACE INTO USUARIOS(ID, NOME, TELEFONE, ENDERECO)
SELECT 2, 'José Pedro dos Santos', TELEFONE 
WHERE ID = 2