asked on

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

SOLUTION

theGhost_k8

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

thejaswipadmanabha

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());
?>

tdot

AFTER SQL QUERY
$id = mysql_insert_id();

CMDAI

ASKER

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?

ASKER CERTIFIED SOLUTION

CMDAI

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CMDAI

ASKER

oh ok i got it

I thought id was a variable , id is the index column.

CMDAI

ASKER

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

Ioannis Anifantakis

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.