REPLACE

Overview

Adds a new row or rows to the table, replacing the row in the event of a conflict with Primary or Unique index keys.

USAGE

Common Syntax:

REPLACE
   [INTO] <tbl_name>
      [(<col_name> [, <col_name>] ...)]
      { {VALUES | VALUE} (<value_list>) [, (<value_list>)] ... }

REPLACE
   [INTO] <tbl_name>
      SET <col_name> = {<value> | DEFAULT} [, <col_name> = {<value> | DEFAULT}] ...

REPLACE
   [INTO] <tbl_name>
      [(<col_name> [, <col_name>] ...)]
      {SELECT ...}

DETAILS

SYNONYMS

SCHEMA

PARAMETERS

SKYSQL

PRIVILEGES

EXAMPLES

REPLACE

To add or replace a row of data in a table:

REPLACE INTO customers (customer_id, customer_name, customer_email, customer_status)
   VALUES (1, 'East Corp', 'east@example.com', 'inactive');

Here is the added row:

SELECT * FROM customers WHERE customer_id = 1;
+-------------+---------------+------------------+-----------------+
| customer_id | customer_name | customer_email   | customer_status |
+-------------+---------------+------------------+-----------------+
|           1 | East Corp     | east@example.com | inactive        |
+-------------+---------------+------------------+-----------------+

To replace existing row data with new values, including conflicting primary key customer_id which is already present in the table:

REPLACE INTO customers (customer_id, customer_name, customer_email, customer_status)
   VALUES (1, 'East GmbH', 'east@example.com', 'active');

Here is the replaced row:

SELECT * FROM customers WHERE customer_id = 1;
+-------------+---------------+------------------+-----------------+
| customer_id | customer_name | customer_email   | customer_status |
+-------------+---------------+------------------+-----------------+
|           1 | East GmbH     | east@example.com | active          |
+-------------+---------------+------------------+-----------------+

Omit Column Names

Column names can be omitted if the supplied values follow the order in the table definition:

REPLACE INTO customers VALUES (2, 'East S.p.A.', 'east@example.net', 'inactive');

SET Clause

Column order does not matter when the SET clause is used:

REPLACE INTO customers
   SET customer_name = 'East International NGO', customer_email = 'east@example.org',
       customer_status = 'active', customer_id = 3;

Multi-Row Replace

To replace multiple rows of data, specify multiple rows separated by commas after the VALUES clause:

REPLACE INTO customers VALUES
   (4, 'West Corp', 'west@example.com', 'active'),
   (5, 'West LLC', 'west@example.net', 'active'),
   (6, 'West NGO', 'west@example.org', 'inactive');

REPLACE .. SELECT

To replace data based on results of a SELECT statement:

REPLACE INTO customers
   SELECT prospects_id, prospects_name, prospects_email, prospects_status
      FROM prospects;

ERROR HANDLING

FEATURE INTERACTION

RESPONSES

DIAGNOSIS

ISO 9075:2016

CHANGE HISTORY

Release Series

History

23.09

  • Present starting in MariaDB Xpand 23.09.1.

6.1

  • Present starting in MariaDB Xpand 6.1.0.

6.0

  • Present starting in MariaDB Xpand 6.0.3.

5.3

  • Present starting in MariaDB Xpand 5.3.13.

Release Series

History

6.0

  • Present starting in MariaDB Xpand 6.0.3.

5.3

  • Present starting in MariaDB Xpand 5.3.13.

Release Series

History

6.1

  • Present starting in MariaDB Xpand 6.1.0.

EXTERNAL REFERENCES