On Tue, 14 Sep 2004, Yves Goergen wrote: > Hi, > I can vaguely remember there was something like "INSERT... on duplicate > key UPDATE..." in MySQL, but the documentation search is almost as > useful as I'm used to - it cannot tell me anything about this. Can you > please? How does this work, what's the syntax? Is this ANSI-SQL standard > (or compatible to other DBMS)? Or is it even documented?
It's covered on the INSERT page: http://dev.mysql.com/doc/mysql/en/INSERT.html "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; 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. As of MySQL 4.1.1, you can use the VALUES(col_name) function in the UPDATE clause to refer to column values from the INSERT part of the INSERT ... UPDATE statement. In other words, VALUES(col_name) in the UPDATE clause refers to the value of col_name that would be inserted if no duplicate-key conflict occurred. This function is especially useful in multiple-row inserts. The VALUES() function is meaningful only in INSERT ... UPDATE statements and returns NULL otherwise. Example: mysql> INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6) -> ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b); That statement is identical to the following two statements: mysql> INSERT INTO table (a,b,c) VALUES (1,2,3) -> ON DUPLICATE KEY UPDATE c=3; mysql> INSERT INTO table (a,b,c) VALUES (4,5,6) -> ON DUPLICATE KEY UPDATE c=9; When you use ON DUPLICATE KEY UPDATE, the DELAYED option is ignored. " -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
