MYSQL INSERT ID

Im writing stored procedure, MYSQL version 5.1

how can i get the last added or updated ID.
The table has a primary key called `stock_id`

INSERT INTO stocks 
		SET 	ctns_or_loose = usp_remaining_loose ,
			inventory_id = usp_inventory_id ,
			pcs_in_ctn = 0 ,
			stock_location_id  = usp_stock_id
		ON DUPLICATE KEY UPDATE ctns_or_loose = ctns_or_loose + usp_remaining_loose ;

Open in new window

LVL 1
CMDAIAsked:
Who is Participating?
 
CMDAIConnect With a Mentor Author Commented:
I found this guy doing it but cant get his example to work....
http://www.sreeju-on-web.co.cc/?p=10

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;
0
 
theGhost_k8Connect With a Mentor Database ConsultantCommented:
may be u can use last_insert_id stored in a variable:

SELECT LAST_INSERT_ID() INTO lstInsId;
0
 
thejaswipadmanabhaCommented:
ideally SELECT LAST_INSERT_ID(); would work a sample PHP code is as below
<?php
$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
mysql_select_db('mydb');

mysql_query("INSERT INTO mytable (product) values ('kossu')");
printf("Last inserted record has id %d\n", mysql_insert_id());
?>
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
tdotCommented:
AFTER SQL  QUERY
$id = mysql_insert_id();
0
 
CMDAIAuthor Commented:
Not using php here....

Thank this works
SELECT LAST_INSERT_ID()

how about if im using ON DUPLICATE KEY UPDATE,
is there a way to find out which which  key was updated?
0
 
CMDAIAuthor Commented:
oh ok i got it

I thought id was a variable , id is the index column.
0
 
CMDAIAuthor Commented:
Thank for your help, this is my result
INSERT INTO stocks 
SET 	ctns_or_loose = usp_remaining_loose ,
        inventory_id = usp_inventory_id ,
	pcs_in_ctn = 0 ,
	stock_location_id = usp_stock_id
ON DUPLICATE KEY UPDATE stock_id = LAST_INSERT_ID(stock_id) , ctns_or_loose = ctns_or_loose + usp_remaining_loose ;

Open in new window

0
 
Ioannis AnifantakisSoftware EngineerCommented:
what I use to get last inserted id is the following, but you have to be inside a transaction to do that.

1) start transaction;
2) insert blah blah blah
3) select @@identity as id;
4) commit;

@@identity always returns last id number of your insert.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.