The Ultimate Guide to High Availability with MariaDB

REPLACE...RETURNING

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

Syntax

REPLACE [LOW_PRIORITY | DELAYED]
 [INTO] tbl_name [PARTITION (partition_list)] [(col,...)]
 {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
[RETURNING select_expr 
      [, select_expr ...]]

Or:

IREPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name [PARTITION (partition_list)]
    SET col={expr | DEFAULT}, ...
[RETURNING select_expr 
      [, select_expr ...]]

Or:

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name [PARTITION (partition_list)] [(col,...)]
    SELECT ...
[RETURNING select_expr 
      [, select_expr ...]]

Contents

  1. Syntax
  2. Description
  3. Examples

Description

It is possible to return a resultset of the replaced rows to the client by using the syntax REPLACE ... RETURNING select_expr [, select_expr2 ...]] This returns the listed columns for all the rows that are replaced, 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.

Examples

Simple REPLACE statement

MariaDB [test]> REPLACE INTO t2 VALUES (1,'Leopard'),(2,'Dog') RETURNING id2, id2+id2 
as Total ,id2|id2, id2&&id2;
+-----+-------+---------+----------+
| id2 | Total | id2|id2 | id2&&id2 |
+-----+-------+---------+----------+
|   1 |     2 |       1 |        1 |
|   2 |     4 |       2 |        1 |
+-----+-------+---------+----------+
2 rows in set (0.065 sec)

Using stored functions in RETURNING

MariaDB [test]> DELIMITER |
MariaDB [test]> CREATE FUNCTION f(arg INT) RETURNS INT
    -> BEGIN
    ->   RETURN (SELECT arg+arg);
    -> END|
Query OK, 0 rows affected (0.173 sec)
MariaDB [test]> DELIMITER ;
MariaDB [test]> PREPARE stmt FROM "REPLACE INTO t2 SET id2=3, animal2='Fox' RETURNING f2(id2),
UPPER(animal2)";
Query OK, 0 rows affected (0.002 sec)
Statement prepared

MariaDB [test]> EXECUTE stmt;
+---------+----------------+
| f2(id2) | UPPER(animal2) |
+---------+----------------+
|       6 | FOX            |
+---------+----------------+
1 row in set (0.094 sec)

Subqueries in the statement

MariaDB [test]> REPLACE INTO t1 SELECT * FROM t2 RETURNING (SELECT id2 FROM t2 WHERE 
id2 IN (SELECT id2 FROM t2 WHERE id2=1)) AS new_id;
+--------+
| new_id |
+--------+
|      1 |
|      1 |
|      1 |
|      1 |
+--------+
4 rows in set (0.005 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 REPLACE 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.