SQL Replace Into question

7
votes

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?

3
sqlmysqlreplace

3 Answers

8
votes
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.

0
votes

It depends on what the primary key and/or unique constraints are on the table. If there is no primary key or unique contraints, it is no different from a basic INSERT statement.

The documentation gives a reasonably clear explanation: http://dev.mysql.com/doc/refman/5.0/en/replace.html

0
votes

There are two different operators for insert and update

update tblNames set FirstName="John", LastName="Smith" where FirstName="John" and LastName="Jones"

this will rename John Jones to John Smith

insert into tblNames (FirstName, LastName) values ("John", "Smith")

this will add a new entry (but may fail if there is already John Smith in the table and there is a unique constraint on FirstName/LastName)