How to Retrieve Inserted Row Values in a Single MySQL Query?

Retrieving Inserted Row Values in a Single MySQL Query

When inserting a new row into a MySQL table, it's often necessary to retrieve the newly inserted values for further processing or referencing. Here's how to insert a row and simultaneously acquire its content in a single query.

The provided query:

INSERT INTO `items` (`item`, `number`, `state`) 
(SELECT '3', `number`, `state` FROM `item_bug` WHERE `id`='3')

inserts a row into the items table, selecting data from the item_bug table with id equal to 3. However, this query doesn't provide a way to retrieve the newly inserted record.

To achieve both insertion and retrieval in a single query, you can utilize the LAST_INSERT_ID() function. This function returns the ID of the last inserted row. By combining it with an additional query, you can obtain the details of the inserted record.

Here's the modified query:

INSERT INTO `items` (`item`, `number`, `state`) 
(SELECT '3', `number`, `state` FROM `item_bug` WHERE `id`='3');

SELECT * FROM `items` WHERE `id` = LAST_INSERT_ID();

This query first inserts the row into the items table. Then, it executes the SELECT query, which fetches all the columns from the items table for the row with an id equal to the last inserted ID.

The above is the detailed content of How to Retrieve Inserted Row Values in a Single MySQL Query?. For more information, please follow other related articles on the PHP Chinese website!