Get id of records updated with ON DUPLICATE KEY UPDATE

Question

I want to know if there is a way to get the ID of records updated with ON DUPLICATE KEY UDATE.

For example, I have the users table with the following schema:

CREATE TABLE `users` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL,
  `username` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx-users-email` (`email`)
);

and insert some users:

INSERT INTO users (email, username) VALUES ("pioz@example.org", "pioz"),("luke@example.org", "luke"),("mike@example.org", "mike");

the result is:

+----+------------------+----------+
| id |      email       | username |
+----+------------------+----------+
|  1 | pioz@example.org | pioz     |
|  2 | luke@example.org | luke     |
|  3 | mike@example.org | mike     |
+----+------------------+----------+

Now I want to know if, with a query like the following one, is possible to get the ID of the updated records:

INSERT INTO users (email, username) VALUES ("luke@example.org", "luke2"),("mike@example.org", "mike2") ON DUPLICATE KEY UPDATE username=VALUES(username);

In this example ID 2 and 3.

[This question](https://stackoverflow.com/questions/8873805/update-delete-in-mysql-and-get-the-list-of-affected-row-ids) is similar and shows on [this answer](https://stackoverflow.com/a/8873956/3536236) using MySQL variables to collect such data. — Martin, Mar 21 '22 at 13:56

score 1 · Answer 1 · answered Mar 21 '22 at 14:58

It seems that the only solution is to used a stored procedure. Here is an example for one row, which could be expanded.
See dbFiddle link below for schema and testing.

CREATE PROCEDURE add_update_user(IN e_mail VARCHAR(25), IN user_name VARCHAR(25) )
BEGIN
    DECLARE maxB4 INT DEFAULT 0;
    DECLARE current INT DEFAULT 0;
    
    SELECT MAX(ID) INTO maxB4 FROM users;
    INSERT INTO users (email, username) VALUES 
       (e_mail, user_name) 
       ON DUPLICATE KEY UPDATE username=VALUES(username);
      
    SELECT ID INTO current FROM users WHERE email =e_mail;
    SELECT CASE WHEN maxB4 < current THEN CONCAT('New user with ID ', current, ' created')
      ELSE CONCAT('User with ID ', current, ' updated') END Report;
      /*SELECT CASE WHEn maxB4 < current THEN 1 ELSE 0 END;*/
END

call add_update_user('jake@example.com','Jake');

| Report                     |
| :------------------------- |
| New user with ID 6 created |

call add_update_user('jake@example.com','Jason');

| Report                 |
| :--------------------- |
| User with ID 6 updated |

db<>fiddle here

score 0 · Answer 2 · answered Jul 09 '22 at 18:34

0

Plan A: Use the technique in the ref manual -- see LAST_INSERT_ID()

Plan B: Get rid of id and make email the PRIMARY KEY

answered Jul 09 '22 at 18:34

Rick James

135,179
13
127
222

Get id of records updated with ON DUPLICATE KEY UPDATE

2 Answers2