delete

Removes rows from a table.

Syntax

delete from TABLE_NAME [ where CONDITION ];

• Without where, all the rows go: delete from T; is the same as truncate table T;, but slower.
• You need the DELETE privilege.
• If a partition of the table has no available server, the server refuses to delete any row.

Sub-queries

The condition may contain a sub-query:

delete from visitors where id not in (select visitor_id from visit_history where last_meeting > now() - interval 1 year);

The sub-query is not a SynSQL query: each data broker runs it on its own partitions. If both tables have the same hash key (the visitor's id) and the same number of partitions, the result is the one you expect. In other designs, you can delete a visitor whose recent visit is stored on another data broker.

Wide tables

With a where, the server first reads the hash key and the columns of the condition to find the rows, then deletes them one by one by their key. On a big table, this takes time. Without where it empties the table at once.

The answer says how many rows were deleted.