> Most likely, MySQL has done a 'DELETE' followed by an 'INSERT' - i.e. two rows.
According to: http://dev.mysql.com/doc/refman/4.1/en/insert-on-duplicate.html The rows-affected value is 1 if the row is inserted as a new record and 2 if an existing record is updated. But what happens if the row is neither inserted nor updated? Right now it appears to returns '2'. I think it should return '0'. Cheers, Richard. -----Original Message----- From: Richard Dale [mailto:[EMAIL PROTECTED] Sent: Friday, 19 May 2006 11:23 a.m. To: mysql@lists.mysql.com Subject: INESRT ... ON DUPLICATE KEY - rows affected incorrect? Mysql 4.1.16 running on Fedora Core 4 (Linu) X86_64 with InnoDB tables I have a table called which has a (combined) primary key of assetid and date plus several data fields (open, high, low, close - all doubles, and voume - a bigint). See the bottom of the email for the table definition. INSERT INTO price (assetid,date,open,high,low,close,volume) VALUES (202690,'2006-05-18','334.25','334.25','334.25','334.25','1') ON DUPLICATE KEY UPDATE open='334.25',high='334.25',low='334.25',close='334.25',volume='1'; The first time around, the insert works fine: Query OK, 1 row affected (0.03 sec); The second time around, when there should be neither an insert nor an update that affects any rows I get: Query OK, 2 rows affected (0.05 sec) To check that the update wasn't in fact updating any rows, I tried: UPDATE price SET open='334.25',high='334.25',low='334.25',close='334.25',volume='1' WHERE assetid=202690 AND date='2006-05-18'; Query OK, 0 rows affected (0.02 sec) Rows matched: 1 Changed: 0 Warnings: 0 So, it appears that the "rows affected" returned by an INSERT ... ON DUPLICATE KEY is incorrect. Can anyone else confirm that this is the correct behaviour? Thanks, Richard. ----- Table definition: CREATE TABLE `price` ( `assetid` int(11) NOT NULL default '0', `date` date NOT NULL default '0000-00-00', `open` double default NULL, `high` double default NULL, `low` double default NULL, `close` double default NULL, `volume` bigint(20) default NULL, PRIMARY KEY (`assetid`,`date`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
