⪢ Updating documents

REPLACE vs UPDATE

You can modify existing data in an RT or PQ table by either updating or replacing it.

UPDATE replaces row-wise attribute values of existing documents with new values. Full-text fields and columnar attributes cannot be updated. If you need to change the content of a full-text field or columnar attributes, use REPLACE.

REPLACE works similarly to INSERT except that if an old document has the same ID as the new document, the old document is marked as deleted before the new document is inserted. Note that the old document does not get physically deleted from the table. The deletion can only happen when chunks are merged in a table, e.g., as a result of an OPTIMIZE.

REPLACE

REPLACE works similarly to INSERT, but it marks the previous document with the same ID as deleted before inserting a new one.

For HTTP JSON protocol, two request formats are available: Manticore and Elasticsearch-like. You can find both examples in the provided examples.

‹›
  • SQL
  • JSON
  • Elasticsearch
  • PHP
  • Python
  • javascript
  • Java
  • C#
📋
REPLACE INTO products VALUES(1, "document one", 10);
‹›
Response
Query OK, 1 row affected (0.00 sec)

REPLACE is available for both RT and PQ tables.

When you run a REPLACE, the previous document is not removed, but it's marked as deleted, so the table size grows until chunk merging happens, and the marked documents won't be included. To force a chunk merge, use the OPTIMIZE statement.

The syntax of the REPLACE statement is the same as the INSERT statement syntax.

REPLACE INTO table [(column1, column2, ...)]
    VALUES (value1, value2, ...)
    [, (...)]

To use the HTTP JSON interface with REPLACE, use the /replace endpoint. There's also a synonym endpoint, /index.

Bulk replace

You can replace multiple documents at once. Check bulk adding documents for more information.

‹›
  • SQL
  • JSON
  • PHP
  • Python
  • javascript
  • Java
  • C#
📋
REPLACE INTO products(id,title,tag) VALUES (1, 'doc one', 10), (2,' doc two', 20);
‹›
Response
Query OK, 2 rows affected (0.00 sec)