Last updated on 10 April 2021
Many DBMSs support a way to get information about a rows we just deleted, updated, or inserted. The syntax varies depending on the DBMS, and some of them allow more options than others. Currently MySQL has no similar feature. MariaDB supports the
keyword for the following statements:
Feature request: MDEV-5092 – Implement UPDATE with result set.
MariaDB RETURNING statements
Let’s go through the supported
Sometimes we need to run a
just before a
. For example when we want to delete an email from a list and then print the message “The email email@example.com has been removed from our archives”.
allows us to run one less query. Returning the number of queries that your application runs may be important for performance.
DELETE FROM mailing_list WHERE id = 24 RETURNING email AS deleted_email;
s do not support
is very similar: it inserts a row and returns the columns we want to know, without running an additional
. Actually the values we insert are written in our
statement, but with some exceptions:
- The value of the
AUTO_INCREMENTprimary key. This is the most common reason to use
DEFAULTvalues assigned by the database, and not know by the application. Since
DEFAULTvalues are logically part of a column definition, it is correct to write them in the database. And since
DEFAULTvalues may change over time (just like any other metadata), it is better to only write them in the database, to avoid the hassle of keeping defaults in sync in different places. But without
INSERT RETURNING, the required effort would be unreasonable.
- Generates columns values can be retrieved by the application, for the same reasons. Generated columns can be seen as
DEFAULTvalues that cannot be changed by the user.
BEFORE INSERTtriggers may modify the values we insert.
INSERT RETURNINGwill return the modified values.
- We hardly know for sure which rows are inserted when we run
INSERT ... SELECT. Using
INSERT SELECT RETURNINGsolves this problem.
Another case is
INSERT ... ON DUPLICATE KEY UPDATE
. It should always be used when we need to insert a row or change it if it exists, as it allows to do that with a single statement. (As mentioned elsewhere, we shouldn’t use
But then, there are additional reasons why we may not know the value of a column:
- We insert a certain value (
VALUESclause) if the row does not exist, and a different value (
UPDATEclause) if the row exists.
- We don’t modify a value in case the row exists: it value won’t change, but it is unknown.
works with multi-row inserts too.
MariaDB [test]> INSERT INTO user (name, is_smart) VALUES ('Boris Johnson', FALSE), ('Peter Capaldi', TRUE) RETURNING id AS pk; +----+ | pk | +----+ | 1 | | 2 | +----+
(These values are clearly the output of a random generator)
ORMs often run
followed by a
. This is probably because of the reasons listed above, in particular they probably want to know the the row’s id. My advice is to run
instead, when using MariaDB; or some equivalent syntax when using other DBMSs that support it.
As mentioned above, I don’t believe it is a good idea to use
, in most cases. Use
INSERT ... ON DUPLICATE KEY UPDATE
Checking DELETEd or INSERTed rows
to check how many rows were deleted/inserted is not necessary. We can use
instead (every API has an equivalent function), or
in stored procedures. Also, if you use a transactional engine all rows are inserted or no row is.
to check if the operation succeeded is not necessary. We can just check if the statement failed with an error instead.
State of the art
limitations and features that work fine with
This article will be updated if some limitations are lifted of
Db2 supports a way to get the resultsets before triggers and foreign keys are executed. MariaDB does not have this feature.
statements cannot be used in these contexts:
CREATE TABLE SELECT.
SELECT * FROM (.
INSERT RETURNING) WHERE ...
- With cursors in stored procedures.
RETURNINGdoes not support aggregate functions.
The following features and syntax can be used in a
RETURNING *(return all columns).
- Built-in functions and operators, eg:
RETURNING UPPER(name), price - 1.
- Stored functions, eg:
can also be used in these contexts:
- Prepared statements.
- Stored procedures body.
- MySQL/MariaDB: use SQL properly to run less queries, on Federico-Razzoli.com
can often be used to run one less query, making our applications faster and saving resources.
If you noticed any mistake or have good practices to suggest, please drop a comment below. Comments contribute to make websites better, and here they are always very welcome.