Good afternoon, I have had reason to use the new (as of 4.1.1) INSERT .. ON DUPLICATE KEY UPDATE syntax in MySQL. However, I am a bit confused as to the return value. Issuing the INSERT .. ON DUP KEY UP statement, upon finding a duplicate key and updating that record, mysql-client returns "2 rows affected"; upon inserting a unique row, it returns the expected "1 row affected". This feature is also mirrored when using the MySQL API in both perl and C (no surprise there; same underlying engine).
I have theorized that the 1st row is referring to a row that temporarily existed during the insert, then was deleted, and the 2nd row is the row that was updated (or vice versa), but this is fairly dependent upon how this statement was implemented; normally a key constraint shouldn't actually succeed and should return an error before modifying the table (i.e. 0 rows affected). Thus I would like to address the developers and inquire about this particular behaviour. Further specific query information is below (sometimes an illustration helps). Thank you, Jason McManus <[EMAIL PROTECTED]> --- snip --- mysql> desc kanjidic_user_study_kanji_xref; +-------------+------------------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+------------------+------+-----+-------------------+-------+ | user_id | int(10) unsigned | | PRI | 0 | | | kanji_id | int(10) unsigned | | PRI | 0 | | | create_time | timestamp | YES | | CURRENT_TIMESTAMP | | +-------------+------------------+------+-----+-------------------+-------+ 3 rows in set (0.00 sec) mysql> select * from kanjidic_user_study_kanji_xref where user_id=3; +---------+----------+---------------------+ | user_id | kanji_id | create_time | +---------+----------+---------------------+ | 3 | 38 | 2004-10-15 06:10:02 | | 3 | 392 | 2004-10-15 05:44:45 | | 3 | 701 | 2004-10-15 04:49:54 | +---------+----------+---------------------+ 3 rows in set (0.00 sec) mysql> insert into kanjidic_user_study_kanji_xref (user_id,kanji_id,create_time) values (3,38,NOW()) ON DUPLICATE KEY UPDATE create_time=NOW(); Query OK, 2 rows affected (0.02 sec) <-- HUH? mysql> select * from kanjidic_user_study_kanji_xref where user_id=3; +---------+----------+---------------------+ | user_id | kanji_id | create_time | +---------+----------+---------------------+ | 3 | 38 | 2004-10-15 09:17:41 | <-- only change... | 3 | 392 | 2004-10-15 05:44:45 | | 3 | 701 | 2004-10-15 04:49:54 | +---------+----------+---------------------+ 3 rows in set (0.00 sec) mysql> insert into kanjidic_user_study_kanji_xref (user_id,kanji_id,create_time) values (3,389,NOW()) ON DUPLICATE KEY UPDATE create_time=NOW(); Query OK, 1 row affected (0.00 sec) <-- This is right. mysql> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
