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]
