CODEWITHSUNDEEP
mysqlsql
Peter
Peter

Reputation: 9143

Get the last inserted id using just MySQL

I would like to be able to get the last inserted id in a mysql table using just mysql.

Example:

INSERT INTO groups (name, customer_id)
SELECT
groups.name as name,
'2' as customer_id
FROM products
LEFT JOIN groups ON products.group_id=groups.id
WHERE customer_id=1;

Now that I have selected a group from one user and created a new group with the same name for another user, I would like to update all products to have the new group id instead of the old one.

UPDATE products SET
group_id=...last inserted id...
WHERE customer_id=2;

Is this possible using just mysql?

Upvotes: 0

Views: 127

Answers (1)

Poul Kruijt
Poul Kruijt

Reputation: 71961

Use LAST_INSERT_ID()

UPDATE products SET
group_id = LAST_INSERT_ID()
WHERE customer_id=2;

That should work..

You should use a transaction to prevent an INSERT if the UPDATE went wrong and the other way around. This helps the integrity of your data 

START TRANSACTION;

INSERT INTO groups (name, customer_id)
   SELECT
     groups.name as name,
     '2' as customer_id
   FROM products
   LEFT JOIN groups ON products.group_id=groups.id
   WHERE customer_id=1;

UPDATE products SET
group_id = LAST_INSERT_ID()
WHERE customer_id=2;

COMMIT;

Upvotes: 2

Related Questions