What is REPLACE INTO for in MYSQL?

Asked

Viewed 10,250 times

19

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

Example:

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

  • As in the INSERT the REPLACE checks whether Primary Key is repeated and if it is overwrites instead of adding values without checking anything. For more references REPLACE Mysql documentation

1 answer

27


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

Command will do what ?

I gave the name of the fields in the first couple of parentheses, ID and NAME. And VALUES put the ID 2 referring to José and in the NAME field the new value I want you to update.

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

That is, if the record exists updates, if not inserts.

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

Would look like this:

ID   NOME
1    Diego
2    José Pedro
3    Bigown

Remarks

1 - No need to use WHERE if the ID.

2 - The ID shall be auto_increment.

3 - If the table has more fields and they are not defined in INTO and VALUES, values will be deleted. Unfortunately this is how.

4 - You can go around what happens in point 3 with a SELECT in the same table. There it is necessary the WHERE in the SELECT.

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

  • The WHERE, in this case, it is not necessary?

  • @Earendul Well remembered, it is not necessary, seen to already be embedded in the column ID.

  • What if the table has another field (phone for example) and I don’t pass it to the list of columns that will be affected? It will remain and the other data will be overwritten?

  • 1

    They will all be replaced. The command deletes everything that is not defined.

  • What you can do is REPLACE INTO with a SELECT.

  • Taking advantage of the topic, it would not be easier to use the UPDATE ?

  • No, William. Depending on the case, REPLACE INTO is more appropriate. No wonder there is such a resource.

Show 2 more comments

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.