SQL Replace Into question

Question

With Replace Into, if I have two fields. FirstName LastName. The table has John Smith in it, if I was to run REPLACE INTO tblNames (FirstName, LastName) VALUES (John, Jones) Would that replace Smith with Jones, or create a new name?

What determines if its an Update or and Insert?

Answer 1

REPLACE
INTO    tblNames (FirstName, LastName)
VALUES  ('John', 'Jones')

If there a unique constraint of any kind on FirstName, LastName or their combination, and it is violated, the records gets deleted and inserted with the new values.

The record will be replaced if any of the conditions is satisfied:

• FirstName is UNIQUE and there is a John in the table,
• LastName is UNIQUE and there is a Jones in the table,
• FirstName, Lastname is UNIQUE and there is a John Jones in the table.

Note that REPLACE operation is an INSERT possibly following a DELETE which will always affect the table.

In the newer versions of MySQL, you should use INSERT … ON DUPLICATE KEY UPDATE.