Outdated Version
You are viewing an older version of this section. View current production version.
REPLACE
If the table has a PRIMARY KEY
or UNIQUE
index, REPLACE
will check rows for a matching value and, if a match is found, will delete the old row and replace it with the new row. If no match is found, or if there is no PRIMARY KEY
or UNIQUE
index, the REPLACE
command operates exactly like INSERT, simply inserting a new row into the table.
Syntax
REPLACE [INTO] tbl_name [(col_name,...)]
{VALUES | VALUE} (expr,...),(...),...
REPLACE [INTO] tbl_name [(col_name,...)]
SELECT ...
REPLACE [INTO] tbl_name
SET col_name=expr, ...
Return Type
Returns a count indicating the number of rows affected, consisting of the sum of rows deleted and inserted.
Remarks
REPLACE
requires bothINSERT
andDELETE
privileges for the table.- MemSQL supports constants,
DEFAULT
, or nullary builtins such asNOW()
,RAND()
, orUNIX_TIMESTAMP()
for expressions (expr
) for REPLACEs. - Arithmetic expressions are not currently supported in the
VALUES
clause. REPLACE
queries will fail ifmaximum_table_memory
limit has been reached.REPLACE
queries will fail if there are concurrent ALTER TABLE , CREATE INDEX , or DROP INDEX statements running on the table.- This command must be run on the master aggregator or a child aggregator node (see Node Requirements for MemSQL Commands).
- Writing to multiple databases in a transaction is not supported.
Example
memsql> REPLACE INTO mytbl (seq) VALUE (2);
Query OK, 1 row affected (1.99 sec)
Related Topics