✔ ️Updating documents

REPLACE vs UPDATE

You can change existing data in an RT or PQ index 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 similar 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 index. The deletion can only happen when chunks are merged in an index, e.g. as a result of an OPTIMIZE.

REPLACE

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

  • SQL
  • HTTP
  • PHP
  • Python
  • javascript
  • Java
📋
REPLACE INTO products VALUES(1, "document one", 10);
Response
Query OK, 1 row affected (0.00 sec)

REPLACE is supported for RT and PQ indexes.

The old document is not removed from the index, it is only marked as deleted. Because of this the index size grows until index chunks are merged and documents marked as deleted in these chunks are not included in the chunk created as a result of merge. You can force chunk merge by using OPTIMIZE statement.

The syntax of the REPLACE statement is identical to INSERT syntax:

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

REPLACE using HTTP protocol is performed via the /replace endpoint. There's also a synonym endpoint, /index.

Multiple documents can be replaced at once. See bulk adding documents for more details.

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