Q

Retrieving last row inserted

I have one table, where in rows getting inserted. I would like to get the last row inserted, while the insertion

process continues.  

I think I understand the question, and it is commonly asked in the context of a column value that is automatically generated by the database. In Microsoft Access, this type of column is called an autonumber; in Microsoft SQL/Server, it's called an identity column. In MySQL, it's an auto_increment, and in Oracle, it's a sequence. In PostgreSQL, you can use either a sequence or a serial.

The problem typically arises when inserting a parent record (e.g. order) and then several child records (e.g. order items). Each child record will need the value of the parent's primary key to use as its foreign key.

There are two ways to go about this. The more efficient method involves using a special database function which will return the value that was assigned by the database. In Microsoft Access and SQL/Server, you can use the @@IDENTITY function, and in MySQL, you can use the mysql_insert_id() function. Check your database documentation for details. These functions, if properly invoked, always return the correct value of the just-inserted record.

A strategy that you should be careful with is to attempt to get the last value through the use of a query such as SELECT MAX(ID) FROM... This strategy fails in a multi-threaded environment, because in between the time you do the INSERT, which assigns the next autonumber, and the SELECT MAX(ID), which gets the latest autonumber, some other thread could have executed another INSERT. Some people feel that this problem can be side-stepped by enclosing the INSERT and SELECT MAX statements in a transaction block. While technically correct, this also means that the table is locked longer than it would be for individual statements. This goes against your requirement to retrieve the value "while the insertion process continues." Note that @@IDENTITY and mysql_insert_id() function correctly in a multi-threaded environment.

An alternate strategy is to "query back" the row using columns other than or in addition to the autonumber. Usually, an autonumber column is a surrogate key and the table will have other columns which might provide a way to determine the just-inserted entry. If another column exists which is unique, use it. If no unique column exists, then you can use MAX(ID) in conjunction with other columns.

For example, let's assume that column1 is an autonumber, and that you don't need to mention this column in the INSERT. (This syntax varies from database to database -- for example, in Oracle, you must assign the sequence's NEXTVAL.)

insert into theTable 
      (column2, column3)
 values ('Joe Bfstplk', 'Dogpatch')

select max(column1) 
  from theTable
 where column2 = 'Joe Bfstplk'

It doesn't matter that other threads have inserted additional rows between your INSERT and your SELECT, because you're not going after the latest overall, you're going after the latest with that particular name, presumably the one you just inserted. The benefit here is that you don't need a transaction block.

If column2 is unique, you don't need max(column1) -- just select column1. No subsequent thread will insert another 'Joe Bfstplk' row, so just go get it.

If column2 is not unique, max(column1) along with the WHERE clause should get the column1 value of the row you just inserted. Note that this strategy does have a hole -- some other thread could have inserted another 'Joe Bfstplk' right after yours; you will have to decide if your design is such that you can live with the risk.


 

This was first published in April 2002

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close