Why when using ON DUPLICATE KEY UPDATE or REPLACE, do we have change in 2 lines?

Question

Example

When executing any of the 2 commands, the message is returned:

2 Row(s) affected

Query:

ON DUPLICATE KEY UPDATE:

INSERT INTO `banco`.`tabela` (`id`, `resumo`, `descricao`, `grupo`, `solicitante`) 
VALUES ('129', 'Teste 2', 'Testando 2', '5', '1') 
ON DUPLICATE KEY UPDATE resumo = 'onduplicate', descricao = 'onduplicate', grupo = 5, solicitante = 1;

REPLACE:

REPLACE INTO `banco`.`tabela` (`id`, `resumo`, `descricao`, `grupo`, `solicitante`) 
VALUES ('129', 'Teste 2', 'Testando 2', '5', '1');

---

Doubts

• Why the database performs 2 amendments?
  • What would they be?
  • The 2 forms to make perform the same processes?
• Would have some head start one on top of the other?

Answer 1

According to the information in the manual relating to ON DUPLICATE KEY UPDATE: INSERT ... ON DUPLICATE KEY UPDATE Syntax

With the ON DUPLICATE KEY UPDATE, the mysql_affected_rows() is 1 if the line is inserted as a new and 2 if an existing row is updated.

As to the REPLACE: REPLACE Syntax

When you use a command REPLACE, mysql_affected_rows() will return 2 if the new row replaces an old row. This is because a line was inserted after the duplicate line was deleted.

This fact makes it easy to determine whether REPLACE added or subsitized a line: check if the value of affected lines is 1 (added) or 2 (replaced).

Note that unless the table uses indexes UNIQUE or PRIMARY KEY, use a command REPLACE makes no sense. He becomes equivalent to a INSERT, because there is no index to be used to determine whether a new line duplicates another.

It is possible that the scenario that is bold whatever is happening to him, which is why he returns the result of 2 instead of 1.

The PT-BR manual is here: Mysql Reference Manual 4.1

Difference between REPLACE and ON DUPLICATE KEY UPDATE

REPLACE

1. Try inserting the row in the table
2. If it fails, delete line and insert new line

ON DUPLICATE KEY UPDATE

1. Try inserting the row in the table
2. If it fails, refresh the line

---

Completion (responding to the two new questions)

The processes executed are similar, but in the case of REPLACE there is a "else", which is the fact that he deletes the line and then inserts. The ON DUPLICATE KEY UPDATE is safer in that regard because it just updates the line.

That said, the safest and almost certainly fastest way to run time will be the option ON DUPLICATE KEY UPDATE.