MySQL and the warm blankets of Replace and On Duplicate

Thursday, February 25, 2010

Two very useful features MySQL has that MSSQL doesn't are the 'REPLACE' and the 'ON DUPLICATE'.

I've found these are most helpful when updating existing records, although it is best to understand how they differ and how best to combine them with 'INSERT' statements.

Just for reference a normal 'INSERT' might look like this:

  1: INSERT INTO TABLENAME (productName, productCost, productId)
  2:   VALUES ('new product name', 'new product cost', 'new product id')

And an 'UPDATE' statement might look like this:

  1: UPDATE TABLENAME SET
  2:   productName = 'new product name',
  3:   productCost = 'new product cost'
  4:   WHERE productId = 'existing product id'

'REPLACE' is great if you want to insert new records or update records that have matching unique keys. This is because if a record is not found that has a matching key, the record is just inserted. However, if an existing record is found, the record is first deleted, and then inserted.

An equivalent 'REPLACE' statement looks like this:

  1: REPLACE INTO TABLENAME SET
  2:   productName = 'new product name',
  3:   productCost = 'new product cost',
  4:   productId = 'new or existing product id'

This makes 'REPLACE' handy as you don't need to worry about finding or deleting records when there is data that just needs to be added or renewed, and you can do it all in one statement that takes care of it for you.

Caution is needed with 'REPLACE' because it DELETES RECORDS if they exist, before inserting them. So if you have data that has information in that you don't want to loose then you need..

'ON DUPLICATE'!

Combining an 'INSERT' statement with 'ON DUPLICATE' functions similarly to 'REPLACE' except that it doesn't delete existing records, but gives you the power to decide what fields are replaced with what values.

An 'INSERT' with 'ON DUPLICATE' statement looks like this:

  1: INSERT INTO TABLENAME SET
  2:   productName = 'new product name',
  3:   productCost = 'new product cost',
  4:   productId = 'existing or new product id'
  5:   ON DUPLICATE KEY UPDATE
  6:   productName = Values(productName),
  7:   productCost = Values(productCost)

Notice how the new value for the updated field is chosen from the 'Values' collection passed into the 'INSERT' statement. The new field value can be updated to any value that SQL can accept for the field type being update, and you don't have to update them all.

It can also be used with clauses that you would use with a plain 'INSERT' statement.

To find out more, hop on over to http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html

Posted by John Green at 11:00 AM

Find out more about by contacting us