CODEWITHSUNDEEP
mysqllastinsertid
asker
asker

Reputation: 2199

When will we give an argument to LAST_INSERT_ID()?

Quoted from here:

mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);
mysql> SELECT LAST_INSERT_ID();

What's the use case of the above (what's the benefit )?

I've never used the LAST_INSERT_ID with any parameter before...

UPDATE

My question is why one wants to simulate such kind of sequence this way, when it's automatically available out of the box by auto-incremented primary key?

Upvotes: 5

Views: 2391

Answers (3)

Chindraba
Chindraba

Reputation: 870

A use-case for generating the sequence without the use of AUTO_INCREMENT would be when the resultant ID value is used in more than one table, and it needs to be unique across all the tables. An AUTO_INCREMENT in each table could, most often would, result in the same value existing in each table.

Disregarding normal form requirements, an example could be from a classroom. Every exam given needs a unique ID, yet the exams are recorded in multiple tables: Pop-quizes, weekly tests, mid-term exams, and final exams. Using the OP-provided quote allows the generation of an ID sequence which can be used to be keep the ID unique across all the tables.

Upvotes: 1

Astha
Astha

Reputation: 1734

Query given above is update query and query is just changing the id which was in the row before. Its just updating a rows id so it is needed to update it like this way. Might be user want to change the index of the result row after some work on it. LAST_INSERT_ID will return the last id inserted. And this will be the case only if user want to change the index of the row from the previous one to the one which will be latest in result set.

Upvotes: 0

Jonathan Hall
Jonathan Hall

Reputation: 79576

Also quoted from here:

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(). This can be used to simulate sequences:

Upvotes: 0

Related Questions