Affected Rows Using INSERT ... ON DUPLICATE KEY UPDATE

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Affected Rows Using INSERT ... ON DUPLICATE KEY UPDATE

Post 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?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

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

Post 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..
Post Reply