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 both INSERT and DELETE privileges for the table.
• MemSQL supports constants, DEFAULT, or nullary builtins such as NOW(), RAND(), or UNIX_TIMESTAMP() for expressions (expr) for REPLACEs.
• Arithmetic expressions are not currently supported in the VALUES clause.
• REPLACE queries will fail if maximum_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

• INSERT

Was this Article Helpful?

Thanks For Your Feedback!