REPLACE
This page is part of MariaDB's Documentation.
The parent of this page is: SQL Statements for MariaDB Xpand
Topics on this page:
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 ...}
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;