Page 1 of 1

Affected Rows Using INSERT ... ON DUPLICATE KEY UPDATE

Posted: Fri Jan 23, 2009 4:46 am
by onion2k
I'm using some SQL as follows:

Code: Select all

INSERT INTO `table`
('col1','col2','col3') VALUES 
('val1','val2','val3'), 
('val4','val5','val6'), 
('val7','val8','val9')
ON DUPLICATE KEY UPDATE 
`col1`=VALUES(`col1`),
`col2`=VALUES(`col2`)
This works nicely. But... there doesn't seem to be a way to work out the number of rows that were affected by it. I can compare the cardinality of the table before and after to get the number of inserts, but I'd also like to figure out the number of rows that were updated (if any). I've scoured the manual but I've not found anything useful. Is there a way?

Re: Affected Rows Using INSERT ... ON DUPLICATE KEY UPDATE

Posted: Fri Jan 23, 2009 5:07 am
by VladSun
onion2k wrote:I can compare the cardinality of the table before and after to get the number of inserts, but I'd also like to figure out the number of rows that were updated (if any).
count_before - count_after = inserted rows
count_rows_to_insert - inserted_rows = updated rows ?

Also:
http://dev.mysql.com/doc/refman/5.0/en/ ... -rows.html

Though it's a little bit weird ;)
If you use INSERT ... ON DUPLICATE KEY UPDATE to insert a row, mysql_affected_rows() returns 1 if the row is inserted as a new row and 2 if an existing row is updated.

Re: Affected Rows Using INSERT ... ON DUPLICATE KEY UPDATE

Posted: Fri Jan 23, 2009 5:23 am
by onion2k
yes.. and it's that caveat of it returning 2 on an update that's killing me. I'm using the extended insert syntax so I might be passing 100 records, of which 50 are inserted and 50 are updated ... so the affected rows will be 150. Which is annoying.

EDIT: Although, thinking about it, if I do "(affected_rows - (rows_after - rows_before)) / 2" I think that might give me the number of updates..