SQL Replace In Question

Question

With Replace Into, if I have two fields. First Name Last Name. There is John Smith in the table, if I were to run REPLACE INTO tblNames (FirstName, LastName) VALUES (John, Jones) Will this replace Smith with Jones or create a new name?

What determines if it can be updated or inserted?

Answer 1

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

If there is a unique restriction of any type on FirstName , LastName or a combination thereof, and it is violated, the records are deleted and inserted with new values.

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

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

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

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

Answer 2

It depends on the fact that the primary key and / or unique constraints are in the table. If there is no primary key or unique restrictions, it is no different from the main INSERT statement.

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

Answer 3

There are two different operators for inserting and updating.

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

it will rename John Jones to John Smith

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

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