✔ ️Updating documents

REPLACE vs UPDATE

You can change existing data in an RT or PQ index by either updating or replacing it.

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

REPLACE works similar to INSERT except that if an old document has the same ID as a new document, the old document is marked as deleted before the new document is inserted. Note that the old document is not physically deleted from an index, this only happens 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.

📋
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.

📋
REPLACE INTO products(id,title,tag) VALUES (1, 'doc one', 10), (2,' doc two', 20);
Response
Query OK, 2 rows affected (0.00 sec)