You should select from something unique to that record, max id has nothing to do with a specific record. The best way is to select exactly what you inserted. If there is the possibility of identical rows, add a UUID to each row. the only surefire way to make this multi thread safe is to select exactly what was inserted with a UUID also.
By: Pablo Varando
What is my ID for the record I just inserted?
A question I get quite often from visitors and members of EasyCFM.COM is "if I insert a record, is there a way I can get the ID of the record I just inserted, without having to connect to the database again?"
The answer of course is yes ;) first let me explain how your database structure would look for this example (keep in mind that this is a small example and your table will be different, all you need for this example to work properly is an IDENTITY FIELD in your SQL server. If you need help setting up an identity field, please post a request to the forums [I will be writing a tutorial on this at a later time and will update this page with the link at a latter time... for now please use the forums ;] ...) Now, lets look at the database itself,
| FIELD TYPE | FIELD TYPE | NULL | IDENTITY |
| MEMBER_ID | INT | NO | 1 |
| FIRSTNAME | VARCHAR(50) | YES | |
| LASTNAME | VARCHAR(50) | YES | |
| varchar(250) | YES |
<cfquery name="qGetUser"
datasource="MyDSN">
INSERT INTO users (firstName, lastName, email )
VALUES ( '#FORM.LastName#', '#FORM.LastName#', '#FORM.email#' );
select thisID = @@identity
</cfquery>
<cfoutput>
The ID of the item just inserted is: #qGetUser.thisID#
</cfoutput>
That more or less does it :) Let me explain what it is you saw and what it does....
First you do an insert (as you would any other insert) with the difference that you add a ";" at the end of it. (Notice it in bold). The next thing you did was SELECT thisID = @@identity.... this works similar to a regular SELECT statement... but it basically just returns the ID of the item just inserted to the database!
Questions? Comments? Let me know....
About This Tutorial
Author: Pablo Varando
Total Views: 49,832
Submission Date: August 11, 2003
Last Update Date: June 05, 2009
All Tutorials By This Autor: 47
Skill Level: Intermediate
Platforms Tested: CF4,CF5,CFMXTotal Views: 49,832
Submission Date: August 11, 2003
Last Update Date: June 05, 2009
All Tutorials By This Autor: 47
Discuss This Tutorial
-
-
It seems not to work for MySQL. Stick to Select max(ID)
-
If you are using msAccess (or mysql) you will need to NOT use a ';' and break the query into two seperate queries, which then leads to issues with 'what if two people add a record at the same time' so locking and good checking needs to be used also
-
Hi there. I cant get this to work. Please instruct on setting up the identity. Thanks!
-
It works in newer versions of access. I've used it many many times. you need to use 2 cfqueries, and make sure you cftry and cftransaction them together, so the id will allways be correct. there is also a method like this in mysql. SELECT LAST_INSERT_ID() AS id
-
how can i set a particular field into identity field using mySQlfront. I used mySQL server.
-
mysql_insert_id() is the MySQL version of @@identity.
-
is there any similar variant for this using mysql?
-
@@identity is indeed MS SQL Server specific.
-
I tried this with MS access .. no luck. Looked into the SQL and it seems that this is an only SQL server trick. Ahh man. Back to Select MAX(ID) MC
Advertisement
