Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why are 2 rows affected in my `INSERT ... ON DUPLICATE KEY UPDATE`?

I'm doing an INSERT ... ON DUPLICATE KEY UPDATE for a PRIMARY KEY in the following table:

DESCRIBE users_interests; 
+------------+---------------------------------+------+-----+---------+-------+ | Field      | Type                            | Null | Key | Default | Extra | +------------+---------------------------------+------+-----+---------+-------+ | uid        | int(11)                         | NO   | PRI | NULL    |       | | iid        | int(11)                         | NO   | PRI | NULL    |       | | preference | enum('like','dislike','ignore') | YES  |     | NULL    |       | +------------+---------------------------------+------+-----+---------+-------+ 

However, even though these values should be unique, I'm seeing 2 rows affected.

INSERT INTO users_interests (uid, iid, preference) VALUES (2, 2, 'like') ON DUPLICATE KEY UPDATE preference='like'; 
Query OK, 2 rows affected (0.04 sec) 

Why is this happening?

EDIT

For comparison, see this query:

UPDATE users_interests SET preference='like' WHERE uid=2 AND iid=2; 
Query OK, 1 row affected (0.44 sec) Rows matched: 1  Changed: 1  Warnings: 0 
like image 737
Josh Smith Avatar asked Sep 19 '10 20:09

Josh Smith


People also ask

What is insert on duplicate key update?

INSERT ... ON DUPLICATE KEY UPDATE is a MariaDB/MySQL extension to the INSERT statement that, if it finds a duplicate unique or primary key, will instead perform an UPDATE. The row/s affected value is reported as 1 if a row is inserted, and 2 if a row is updated, unless the API's CLIENT_FOUND_ROWS flag is set.

Is insert on duplicate key update Atomic?

So yes it is atomic in the sense that if the data that you are trying to insert will cause a duplicate in the primary key or in the unique index, the statement will instead perform an update and not error out.


2 Answers

From the manual:

With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row and 2 if an existing row is updated.

like image 163
ChristopheD Avatar answered Oct 12 '22 18:10

ChristopheD


So you know whether you updated a row (duplicate key) or just inserted one: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

like image 33
ontrack Avatar answered Oct 12 '22 18:10

ontrack