MySQL RETURNING Clause: A Guide with Examples
The RETURNING clause in MySQL is a powerful feature that allows you to retrieve values from rows that have been modified in an INSERT, UPDATE, or DELETE statement. It simplifies the process of retrieving and using the modified rows, eliminating the need for additional SQL queries. In this article, we will explore the RETURNING clause and demonstrate its usage with code examples.
Syntax of the RETURNING Clause
The syntax of the RETURNING clause varies depending on the type of statement being used. Let's see the syntax for each statement:
1. INSERT Statement:
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
RETURNING column1, column2, ...;
2. UPDATE Statement:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition
RETURNING column1, column2, ...;
3. DELETE Statement:
DELETE FROM table_name
WHERE condition
RETURNING column1, column2, ...;
Usage and Examples
Now, let's dive into some practical examples to illustrate the usage of the RETURNING clause.
1. INSERT Statement with RETURNING Clause
Consider a scenario where you need to insert a new record into a users table and immediately retrieve the auto-generated user_id for further processing. You can use the RETURNING clause to achieve this:
INSERT INTO users (name, email)
VALUES ('John Doe', 'john.doe@example.com')
RETURNING user_id;
The above query will insert a new row into the users table with the specified name and email. It will also retrieve the user_id of the inserted row.
2. UPDATE Statement with RETURNING Clause
Let's say you want to update the salary of an employee in the employees table and retrieve the updated salary. Here's how you can accomplish it:
UPDATE employees
SET salary = salary * 1.1
WHERE department = 'IT'
RETURNING name, salary;
In the above example, the UPDATE statement increases the salary of employees in the IT department by 10%. The RETURNING clause retrieves the name and salary columns of the updated rows.
3. DELETE Statement with RETURNING Clause
Suppose you need to delete expired orders from an orders table and retrieve the order details for auditing purposes. You can use the RETURNING clause in the DELETE statement as follows:
DELETE FROM orders
WHERE expiry_date < CURDATE()
RETURNING order_id, order_date;
The above query will delete all the expired orders from the orders table and return the order_id and order_date columns of the deleted rows.
Conclusion
The RETURNING clause in MySQL simplifies the process of retrieving modified rows in INSERT, UPDATE, and DELETE statements. It eliminates the need for separate SQL queries to fetch the modified data. By using the examples provided in this article, you can start leveraging the power of the RETURNING clause in your own MySQL projects.
