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

Navigation
3

Example

When you execute 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');

Questions

  • Why does the database run 2 changes ?
    • What would they be?
    • The 2 ways to run the same processes ?
  • Would you have any advantage over each other?
asked by anonymous 13.09.2018 / 12:09

1 answer

3

According to the manual of ON DUPLICATE KEY UPDATE : INSERT ... ON DUPLICATE KEY UPDATE Syntax

  

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

As for REPLACE : REPLACE Syntax

  

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

     

This fact makes it easy to determine whether REPLACE added or substi- tuted a   line: Verify that the value of affected rows is 1 (added) or 2   (substituted).

     

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

It is possible that the scenario that is bold is what is happening to it, hence it returns the result of 2 instead of 1.

The manual is available here: MySQL 4.1 Reference Manual

a>

Difference between REPLACE and ON DUPLICATE KEY UPDATE

REPLACE

  • Attempt to insert row into table
  • If it fails, delete line and insert new line

    • ON DUPLICATE KEY UPDATE

  • Attempt to insert row into table
  • If it fails, update the line

    • Conclusion (responding to the two new questions)

    The processes executed are similar, but in the case of REPLACE there is a "snag", which is that it deletes the line and then inserts it. ON DUPLICATE KEY UPDATE is safer in this respect because it only refreshes the line.

    That said, the safest, and almost certainly faster, way at runtime is the ON DUPLICATE KEY UPDATE option.

        
    13.09.2018 / 13:14
    Make variables created with lapply allocated in their respective dataframe within a list How to finish executing the code in Python 3?