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?
--
Taisuke Yamada <[EMAIL PROTECTED]>
2268 E9A2 D4F9 014E F11D 1DF7 DCA3 83BC 78E5 CD3A
Message to my public address may not be handled in a timely manner.
For a direct contact, please use my private address on my namecard.
signature.asc
Description: OpenPGP digital signature
