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