How do I retrieve the primary key of the updated record?

Question

I have this insert query for my MariaDB 10.6.19 database:

CREATE TABLE manufacturers (
  manufacturers_id int(11) NOT NULL AUTO_INCREMENT key,
  manufacturers_name varchar(32) unique NOT NULL,
  manufacturers_image varchar(64) DEFAULT NULL
);

INSERT INTO manufacturers (manufacturers_id, manufacturers_name, manufacturers_image)
VALUES (1, 'Name', NULL);

Later I want to add the image to the data but I'm unaware if the manufacturer already exists in the database. So I use:

INSERT INTO manufacturers (manufacturers_name, manufacturers_image)
  values ('Name', NULL)
ON DUPLICATE KEY
   UPDATE  manufacturers_name ='Name',
           manufacturers_image = 'MyImage.jpg';

And the record is updated, but how do I retrieve the primary key of the updated record?

use ... returning ... : mariadb.com/docs/server/reference/sql-statements/… — Wiimm
– Wiimm, Commented Oct 4 at 21:33

ValNik · Accepted Answer · 2025-10-06 11:51:51Z

To use INSERT/UPDATE (upsert) method with "ON DUPLICATE KEY" you should have primary key or other UNIQUE constraint.
In example, you do not use primary key column in insert statement - your unique constraint is on column "manufacturers_name", and conflict occurs on this column.

RETURNING caluse is used to return updated/inserted row id (manufacturers_id).

CREATE TABLE manufacturers (
  manufacturers_id int(11) NOT NULL AUTO_INCREMENT key,
  manufacturers_name varchar(32) unique NOT NULL, -- table manufactures UNIQUE constraint 
  manufacturers_image varchar(64) DEFAULT NULL
); 
INSERT INTO manufacturers (manufacturers_id, manufacturers_name, manufacturers_image) 
VALUES (1, 'Name1', NULL);

manufacturers_id	manufacturers_name	manufacturers_image
1	Name1	null

New row insert. Returned new row id

INSERT INTO manufacturers (manufacturers_name,manufacturers_image) 
values ('Name2', NULL ) 
ON DUPLICATE KEY 
  UPDATE  manufacturers_name='Name2'
     , manufacturers_image = 'MyImage2.jpg'
    RETURNING manufacturers_id
;

Query output is

manufacturers_id
2

Table after update

manufacturers_id	manufacturers_name	manufacturers_image
1	Name1	null
2	Name2	null

Update existing row. Updated row id returned

INSERT INTO manufacturers (manufacturers_name,manufacturers_image) 
values ('Name1', NULL ) 
ON DUPLICATE KEY 
  UPDATE  manufacturers_name='Name1-n'
     , manufacturers_image = 'MyImage1.jpg'
    RETURNING manufacturers_id
;

Query output is

manufacturers_id
1

Table after update

manufacturers_id	manufacturers_name	manufacturers_image
1	Name1-n	MyImage1.jpg
2	Name2	null

fiddle

Pay attention: The row/s affected value is reported as 1 if a row is inserted, and 2 if a row is updated Link

Collectives™ on Stack Overflow

How do I retrieve the primary key of the updated record?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related