It seems to me that the _mysql manual_ gives a most straightforward and useful explanation:
"If you specify the ON DUPLICATE KEY UPDATE clause (new in MySQL 4.1.0), and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed. For example, if column a is declared as UNIQUE and already contains the value 1, the following two statements have identical effect:
mysql> INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;
mysql> UPDATE table SET c=c+1 WHERE a=1;
The rows-affected value is 1 if the row is inserted as a new record and 2 if an existing record is updated.
Note: If column b is unique too, the INSERT would be equivalent to this UPDATE statement instead: mysql> UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1; If a=1 OR b=2 matches several rows, only one row is updated! In general, you should try to avoid using the ON DUPLICATE KEY clause on tables with multiple UNIQUE keys."
Or, you might find a more interesting answer on a MySQL list.
Doug
Mark Benson wrote:
As some of you will recall, I enquired on this list about versions of MySQL and PHP a while back regarding and issue wit using the 'ON DUPLICATE KEY UPDATE' command in MySQL INSERT queries. Having upgraded our in-house system to PHP5 and MySQL 4 the issue was no longer a problem.
However I have subsequently enquired with our out-of-house web host and they are still using PHP 4.1.2 and MySQL 3.25.58. As one of the purposes of asking them was to establish if I could easily upload from my database to the remote database, I now have a problem. I was planning to use a compare and update script that used 'ON DUPLICATE KEY UPDATE' command in MySQL INSERT queries to add or change items on the remote server. However I can't, as it's a MySQL4 feature and the database to which I am writing ain't using MySQL4!
Any ideas about how I can create a MySQL3 compliant substitute for the 'ON DUPLICATE KEY UPDATE' Query action?
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
