MySQL and the warm blankets of Replace and On Duplicate
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: <a style="color: rgb(0, 0, 255);" href="http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=INSERT&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99">INSERT</a> <a style="color: rgb(0, 0, 255);" href="http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=INTO&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99">INTO</a> TABLENAME (productName, productCost, productId)2: <a style="color: rgb(0, 0, 255);" href="http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=VALUES&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99">VALUES</a> ('<span style="color: rgb(139, 0, 0);">new product name</span>', '<span style="color: rgb(139, 0, 0);">new product cost</span>', '<span style="color: rgb(139, 0, 0);">new product id</span>')
And an 'UPDATE' statement might look like this:
1: <a style="color: rgb(0, 0, 255);" href="http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=UPDATE&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99">UPDATE</a> TABLENAME <a style="color: rgb(0, 0, 255);" href="http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=SET&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99">SET</a>2: productName = '<span style="color: rgb(139, 0, 0);">new product name</span>',3: productCost = '<span style="color: rgb(139, 0, 0);">new product cost</span>'4: <a style="color: rgb(0, 0, 255);" href="http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=WHERE&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99">WHERE</a> productId = '<span style="color: rgb(139, 0, 0);">existing product id</span>'
'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 <a style="color: rgb(0, 0, 255);" href="http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=INTO&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99">INTO</a> TABLENAME <a style="color: rgb(0, 0, 255);" href="http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=SET&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99">SET</a>2: productName = '<span style="color: rgb(139, 0, 0);">new product name</span>',3: productCost = '<span style="color: rgb(139, 0, 0);">new product cost</span>',4: productId = '<span style="color: rgb(139, 0, 0);">new or existing product id</span>'
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: <a style="color: rgb(0, 0, 255);" href="http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=INSERT&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99">INSERT</a> <a style="color: rgb(0, 0, 255);" href="http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=INTO&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99">INTO</a> TABLENAME <a style="color: rgb(0, 0, 255);" href="http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=SET&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99">SET</a>2: productName = '<span style="color: rgb(139, 0, 0);">new product name</span>',3: productCost = '<span style="color: rgb(139, 0, 0);">new product cost</span>',4: productId = '<span style="color: rgb(139, 0, 0);">existing or new product id</span>'5: <a style="color: rgb(0, 0, 255);" href="http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=ON&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99">ON</a> DUPLICATE <a style="color: rgb(0, 0, 255);" href="http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=KEY&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99">KEY</a> <a style="color: rgb(0, 0, 255);" href="http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=UPDATE&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99">UPDATE</a>6: productName = <a style="color: rgb(0, 0, 255);" href="http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=Values&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99">Values</a>(productName),7: productCost = <a style="color: rgb(0, 0, 255);" href="http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=Values&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99">Values</a>(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
DP
Related Posts
Got a project? Let's work together
Compsoft is a remote working company except for Thursdays when we work together from the Alton Maltings in Alton, Hampshire (UK). Our registered address is detailed below. We'd be delighted to have either a virtual chat with you or to make arrangements to meet in person.