What is REPLACE INTO in MYSQL?

Question

What is REPLACE INTO in MYSQL?

Navigation

#1 by (21 votes)

16

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

Example:

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

mysql

asked by anonymous 03.02.2016 / 13:49

1 answer

Script returning Parse error: syntax error, unexpected end of file What is elegant coding?

score 21 · Accepted Answer

The 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')

The command will do what?

I gave the names 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 that I want to update.

The command will update the registry if it exists, if there is ID 1. If the ID value is set to NULL or '' it will be added a new record.

That is, if the registry exists updates , if not it .

REPLACE INTO USUARIOS (ID, NOME) VALUES (NULL, 'Bigown')

It would look like this:

ID   NOME
1    Diego
2    José Pedro
3    Bigown

Observations

1 - You do not need to use WHERE if ID is set.

2 - The ID must be auto_increment .

3 - If the table has more fields and they are not defined in INTO and VALUES , values will be erased. Unfortunately it is so.

4 - You can work around what happens in point 3 with SELECT in the same table. Then it is required WHERE no SELECT .

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