CODEWITHSUNDEEP
mysql
Perocat
Perocat

Reputation: 1521

Meaning of id = LAST_INSERT_ID(id)

Supposing I have a table:

CREATE TABLE files (
    id_prod INT UNSIGNED NOT NULL DEFAULT PRIMARY KEY AUTO_INCREMENT,
    id_rel INT UNSIGNED,
    name VARCHAR(250),
    other VARCHAR(200),
    UNIQUE INDEX(id_rel , name)
);

and I want to use an unique query to insert/update the data on this table:

INSERT INTO files (id_rel , name)
VALUES ('25', 'test')
ON DUPLICATE KEY UPDATE

now, reading the MySQL manual I read about this:

ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id)

so I thought my query should be:

INSERT INTO files (id_rel , name)
VALUES ('25', 'test')
ON DUPLICATE KEY UPDATE id_prod = LAST_INSERT_ID(id), name = 'TESTED'

but which is the difference if I use only:

INSERT INTO files (id_rel , name)
VALUES ('25', 'test')
ON DUPLICATE KEY UPDATE name = 'TESTED'

?

I cannot understand the meaning of LAST_INSERT_ID(id). What is (id) and what it's supposed to do?

Upvotes: 2

Views: 1525

Answers (1)

Barmar
Barmar

Reputation: 780899

This is only necessary if your application needs to call LAST_INSERT_ID() after performing the INSERT. Normally, LAST_INSERT_ID() will only return a value if you actually inserted a new row into the table, not of there was a duplicate key and it updated the row instead.

From the documentation:

If expr is given as an argument to LAST_INSERT_ID(), the value of the argument is returned by the function and is remembered as the next value to be returned by LAST_INSERT_ID().

If you use the idiom you quoted, LAST_INSERT_ID() will return either the ID of the new row that was inserted or the row that was updated.

Upvotes: 3

Related Questions