How to Retrieve Data from Newly Inserted Row in MySQL with a Single Query?

Retrieve Data from Newly Inserted Row in a Single Query

It is possible to insert a row and retrieve its values using a single query in MySQL. To do this, follow these steps:

1. Execute the INSERT statement to add the new row.
2. Use the LAST_INSERT_ID() function to retrieve the ID of the newly created row. This function returns the ID of the last row inserted by the previous INSERT statement.
3. Combine the INSERT statement with a SELECT statement to retrieve the values inserted in the new row. Here's an example:

<code class="sql">INSERT INTO `items` (`item`, `number`, `state`) 
(SELECT '3', `number`, `state` FROM `item_bug` WHERE `id`='3');

SELECT * FROM `items` WHERE `id`= LAST_INSERT_ID();</code>

In this example, the first statement inserts a new row into the items table by selecting the data from the item_bug table where id is equal to '3'. The second statement selects all columns from the items table where the id is equal to the ID of the newly inserted row, which is retrieved using the LAST_INSERT_ID() function.

This approach allows you to insert a row and get the values inserted in a single query, making it both efficient and convenient.

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