Thanks Quentin... Further to this, I tried changing the types of the fields from DOUBLE to: DECIMAL(10,2) and also VARCHAR(20) to avoid any issues with precision....
The query still returns back Query OK, 2 rows affected (0.05 sec) when the fields are DECIMAL or VARCHAR too. Might be time to file a bug report. Best regards, Richard Dale. Norgate Investor Services - Premium quality Stock, Futures and Foreign Exchange Data for markets in Australia, Asia, Canada, Europe, UK & USA - www.premiumdata.net -----Original Message----- From: Quentin Bennett [mailto:[EMAIL PROTECTED] Sent: Friday, 19 May 2006 12:41 PM To: Richard Dale; mysql@lists.mysql.com Subject: RE: INESRT ... ON DUPLICATE KEY - rows affected incorrect? Its not a problem with precision is it - 332.25 looks OK to you and me, but who knows what it looks like to the CPU? Maybe it sees one of the doubles as slightly different and updates? -----Original Message----- From: Richard Dale [mailto:[EMAIL PROTECTED] Sent: Friday, 19 May 2006 11:48 a.m. To: mysql@lists.mysql.com Subject: RE: INESRT ... ON DUPLICATE KEY - rows affected incorrect? > 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]
