The Ultimate Guide to High Availability with MariaDB

INSERT...RETURNING

You are viewing an old version of this article. View the current version here.

Description

It is possible to return a resultset of the deleted rows for a single table to the client by using the syntax INSERT ... RETURNING select_expr [, select_expr2 ...]] returns the listed columns for all the rows that are inserted, or alternatively, the specified SELECT expression. Any SQL expressions which can be calculated can be used in the select expression for RETURNING clause. So virtual columns and alias, expressions which use various operators like bitwise, logical and arithmetic operators, functions like string function, date-time functions, numeric functions, control flow function, secondary functions and stored functions can be used. Along with this, statements which have subquery and prepared statements can also be used.

Example

Simple INSERT statement

MariaDB [test1]> INSERT INTO t2 VALUES (1,'Dog'),(2,'Lion'),(3,'Tiger'),(4,'Leopard') 
RETURNING id2,id2+id2,id2&id2,id2||id2;
+-----+---------+---------+----------+
| id2 | id2+id2 | id2&id2 | id2||id2 |
+-----+---------+---------+----------+
|   1 |       2 |       1 |        1 |
|   2 |       4 |       2 |        1 |
|   3 |       6 |       3 |        1 |
|   4 |       8 |       4 |        1 |
+-----+---------+---------+----------+
4 rows in set (0.078 sec)

Using stored functions in RETURNING

MariaDB [test1]> DELIMITER |
MariaDB [test1]> CREATE FUNCTION f(arg INT) RETURNS INT
    -> BEGIN
    ->    RETURN (SELECT arg+arg);
    -> END|
Query OK, 0 rows affected (0.156 sec)

MariaDB [test1]> DELIMITER ;

MariaDB [test1]> PREPARE stmt FROM "INSERT INTO t1 SET id1=1, animal1='Bear' RETURNING f(id1), UPPER(animal1)";
Query OK, 0 rows affected (0.002 sec)
Statement prepared

MariaDB [test1]> EXECUTE stmt;
+---------+----------------+
| f(id1)  | UPPER(animal1) |
+---------+----------------+
|       2 | BEAR           |
+---------+----------------+
1 row in set (0.082 sec)

Subqueries in RETURNING clause that return more than one row or column.

Aggregate functions cannot be used in RETURNING clause: Since aggregate functions work on a set of values and if the purpose is to get the row count, ROW_COUNT()with SELECT can be used or it can be used in INSERT...SELECT...RETURNING if the table in RETURNING clause is not the same as the INSERT table.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.