Find what I just inserted

Hey!

I need to find the record_id of the record I just inserted so that I can display the newly created record id.

Right now it looks like this: (I'm using Cold Fusion to insert and retrieve records, but I think this is an SQL question)

INSERT INTO table (value1, value2, value3) values (value1, value, value3)

Select record_id
FROM table
WHERE value1 = value1 AND value2 = value2 AND value3 = value3


The problem is when someone puts in the exact same data for values 1 through 3 I get back only the first record_id, not the last one. Maybe that's the trick right there, add an ORDER BY field_id DESC??

I thought MySQL had a way to grab the last inserted record, or return the record_id while I'm still in the insert statement, that would be preferable.

-Matt

Is record_id an identity field? If so, you can use "select @@identity" to return it.

Lucas Alexander
http://www.alexanderdevelopment.net

Lucasalexander,
Yes, record_id is the identity field.

I will play with this and try it out and report back. Thanks!

Well I finally got a chance to try it out and it DIDN'T work.

I ran the query in Cold Fusion as well as on SQL Server and it didn't return a result. I've never used this before, so any ideas would be appreciated.

select @@identity is only meaningful immediately after an insert statement.

that's the way i do it -- query the row back using the same values of some other columns that i used to insert the row in the first place

let me explain

the record_id is called a surrogate key

as the name suggests, it is "standing in" for the real key

each relational table should have at least one candidate key

a candidate key is a column, or combination of columns, that determines every row uniquely

any candidate key can be declared as the primary key of the table

you may add an auto-incrementing column to a table that didn't have one, and declare it as the primary key, and there are some good reasons for doing so

but the table will still have some other candidate key

(if it doesn't, you're in bigger trouble and may not know it)

so, since the other column(s) that make up the candidate key are unique, you will always get back the row you inserted with those values

in your situation, matt, your other three columns are not a candidate key, because they appear to be allowing duplicates

perhaps you merely overlooked declaring a unique constraint on them

if they cannot be declared unique, you need to either (a) really focus on making the @@IDENTITY technique work, or (b) look for another candidate key

but if you can declare them as unique (after, of course, resolving the dupes that already exist), then you will (a) no longer have your problem, (b) be able to use the @@IDENTITY technique anyway, and (c) be able to pull the row out using those values to get the record_id if that's the way you decide to go

one benefit of querying a row back using the value of a candidate key is that you do not need to do this immediately after the insert, i.e. you do not need to put a transaction block around the INSERT and SELECT

many people use the "select max(record_id)" technique to get back the last auto-incremented value, so of course the transaction block is mandatory, to prevent a second INSERT before the first SELECT gets the highest number

the problem with that (using a transaction block) is that it throttles performance

with the SELECT based on candidate key values, it does not matter if other threads insert additional rows after yours and before you query your row back

however, it does mean that you are actually running two database operations, the INSERT and the SELECT

using the @@IDENTITY function, however, there's only one database operation, the INSERT, and the @@IDENTITY value is supposed to come back along with the database return code

as i said, i don't use the @@IDENTITY tecchnique, but i think all you have to do is just add it to the CFQUERY tag

<CFQUERY NAME="newrow">
INSERT INTO table (col1, col2, col3)
values (value1, value, value3);
SELECT lastid=@@IDENTITY
</CFQUERY>

please let me know if that works, thanks