Hi! On May 15, Taisuke Yamada wrote: > Hi. I found behavior of "ON DUPLICATE KEY" bit odd, and would > like to know if it's a bug or feature. > > I'm trying to "insert" or "add" (not "replace") data of one > table to another table and came up with following statement: > > CREATE TABLE foo (id INT NOT NULL PRIMARY KEY, value INT); > CREATE TABLE bar (id INT NOT NULL PRIMARY KEY, value INT); > > INSERT INTO bar VALUES (1, 100); > > INSERT INTO foo SELECT id, value FROM bar > ON DUPLICATE KEY UPDATE value = value + VALUES(value); > > This INSERT statement works, but is not robust as column order > of table "foo" is not specified. So I add one: > > INSERT INTO foo (id, value) SELECT id, value FROM bar > ON DUPLICATE KEY UPDATE value = value + VALUES(value); > > Now, this one errors with with the message > > ERROR 1110 (42000): Column 'value' specified twice > > Why is this? Can someone tell me what is happening?
Check http://bugs.mysql.com - there were several bugs in name resolution for INSERT ... SELECT ... ON DUPLICATE KEY UPDATE reported recently, I believe yours is one of those. Regards, Sergei -- __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
