Home > Database > Mysql Tutorial > How to Retrieve Inserted Row Values in a Single MySQL Query?

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

DDD
Release: 2024-10-25 08:05:28
Original
1051 people have browsed it

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')
Copy after login

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();
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template