Articles in this section

See more

Understanding the number of rows affected with UPSERT operation

{question}

How do we determine the number of rows affected by the INSERT/UPDATE operations performed by UPSERT?

{question}

{answer}

Singlestore provides the UPSERT operation, which performs the DML operations INSERT and UPDATE in one statement.
An upsert can be achieved by specifying the ON DUPLICATE KEY UPDATE clause of the INSERT statement.
 
When we perform DML operations, we would like to know the number of rows affected by the corresponding operation. Likewise, since UPSERT performs two operations, let's understand how to determine the number of rows updated/inserted with an example.
 
In Singlestore, we score each operation of UPSERT as below. The affected row count will be:
- 0, if no existing row is changed.
- 1, if a new row is Inserted.
- 2, if an existing row is updated.
Example:
Create two tables cust and cust_new with the same structure and inserted 3 records in each table. Note the below in the example:
1. UNIQUE KEY is 'id'.
2. ID '7214' is common between both tables.
 
mysql> CREATE TABLE cust(name VARCHAR(32), id INT(11), orders INT(11), KEY(id) USING CLUSTERED COLUMNSTORE, UNIQUE KEY(id) USING HASH, SHARD KEY(id));
mysql> INSERT INTO cust VALUES('Chris',7214,2);
mysql> INSERT INTO cust VALUES('Elen',8301,4);
mysql> INSERT INTO cust VALUES('Adam',3412,5);

mysql> CREATE TABLE cust_new(name VARCHAR(32), id INT(11), orders INT(11), KEY(id) USING CLUSTERED COLUMNSTORE, UNIQUE KEY(id) USING HASH, SHARD KEY(id));
mysql> INSERT INTO cust_new VALUES('Bill',21,5);
mysql> INSERT INTO cust_new VALUES('Gwen',7214,3);
mysql> INSERT INTO cust_new VALUES('Sam',22,2);

mysql> select * from cust;
+-------+------+--------+
| name  | id   | orders |
+-------+------+--------+
| Adam  | 3412 |      5 |
| Chris | 7214 |      2 |
| Elen  | 8301 |      4 |
+-------+------+--------+
3 rows in set (0.38 sec)

mysql> select * from cust_new;
+------+------+--------+
| name | id   | orders |
+------+------+--------+
| Sam  |   22 |      2 |
| Bill |   21 |      5 |
| Gwen | 7214 |      3 |
+------+------+--------+
3 rows in set (0.33 sec)

 

Scenario 1: On performing an Upsert on cust table onid = 7214, orders value is updated from 2 to 3 on a single record.
Since it is an update operation, the score is 2, and so the number of affected rows is shown as 2 rows affected.

mysql> INSERT INTO cust (ID, ORDERS) VALUES (7214, 3) 
ON DUPLICATE KEY UPDATE ORDERS=3;

Query OK, 2 rows affected (0.40 sec)

Result:

mysql> select * from cust;
+-------+------+--------+
| name | id | orders |
+-------+------+--------+
| Chris | 7214 | 3 |
| Elen  | 8301 | 4 |
| Adam  | 3412 | 5 |
+-------+------+--------+

Scenario 2: On performing Upsert on cust from cust_new: 

mysql> INSERT INTO cust (NAME, ID, ORDERS) SELECT * FROM cust_new ON DUPLICATE KEY UPDATE NAME = VALUES(NAME), ORDERS = VALUES (ORDERS);

Query OK, 4 rows affected (0.36 sec)
Records: 3  Duplicates: 1  Warnings: 0

Result:

mysql> select * from cust;
+------+------+--------+
| name | id | orders |
+------+------+--------+
| Bill | 21   | 5 |
| Gwen | 7214 | 3 |
| Sam  | 22   | 2 |
| Elen | 8301 | 4 |
| Adam | 3412 | 5 |
+------+------+--------+
5 rows in set (0.33 sec)

In the output, Duplicates: 1 shows the number of records updated since it found a duplicate primary key, i.e., ID( = 7214) in this case. Records: 3 shows the total number of records inserted/updated. Since 1 record is updated, the rest of the 2 records are inserts.


As per our documentation, we score 1 for each insert and 2 for each update. So, in this case, since there are 2 inserts and 1 update performed, the number of affected rows is shown as the sum of the scores 4 rows affected.

{answer}

Related articles