SQL - DELETE

Removes one or more records from the database. You can refine the set of records that it removes using the WHERE clause.

Syntax:

DELETE [VERTEX] FROM <Type>|BUCKET:<bucket>|INDEX:<index> [RETURN <returning>]
  [WHERE <Condition>*] [LIMIT <MaxRecords>] [BATCH <batch-size>]
  [TIMEOUT <MilliSeconds>] [UNSAFE] [RETURNING <projection>]
  • VERTEX Deletes only vertices, each with its edges. A target type that is not a vertex type, or an INDEX: target, is refused before anything runs. A record that is not a vertex (reached through a RID, a bucket or a subquery) is not deleted: the statement fails on it, and the vertices it deleted before are rolled back with the statement, except the batches a BATCH already committed. Without it, DELETE FROM deletes any record, and a vertex is deleted with its edges all the same. (Since v26.12.1, issue #11620: the keyword used to be accepted and ignored.)

  • RETURN Defines what values the database returns. It takes one of the following values:

    • COUNT Returns the number of deleted records. This is the default option.

    • BEFORE Returns the number of records before the removal.

  • WHERE Filters to the records you want to delete.

  • LIMIT Defines the maximum number of records to delete.

  • BATCH Defines the number of records to delete in each transaction batch. When set, the command commits the current transaction every <batch-size> records and begins a new one. This is essential for deleting large datasets (e.g., millions of records) that would otherwise consume too much memory in a single transaction.

  • TIMEOUT Defines the time period to allow the operation to run, before it times out.

  • UNSAFE no use, is kept only for compatibility with OrientDB SQL and it could be removed in the future versions of the SQL language.

  • RETURNING PostgreSQL’s trailing form: returns the deleted records as they were before the delete, projected by <projection> (RETURNING * for all their properties), where RETURN BEFORE returns each whole record. RETURN and RETURNING cannot be combined in the same statement. (Since v26.12.1, issue #11392.)

On a TIMESERIES type (since 26.11.1) DELETE removes samples, and it does not take part in the transaction it runs in: the samples are gone, durably, when the statement returns, and a ROLLBACK does not bring them back (the result carries transactional: false). Its WHERE is a time range on the timestamp column and equalities on TAG columns joined by AND; a predicate on a FIELD column, an OR, LIMIT, BATCH and RETURN BEFORE are refused. See Deleting Samples.

Examples:

  • Delete all records with the surname unknown, ignoring case:

ArcadeDB> DELETE FROM Profile WHERE surname.toLowerCase() = 'unknown'
  • Delete all records of the type Document, due to an improper JSON import, or record creation command (note the use of the backticks):

ArcadeDB> DELETE FROM `Document`
  • Delete all records from a large type in batches of 20,000 to avoid loading millions of records into a single transaction:

ArcadeDB> DELETE FROM LargeDataset BATCH 20000
  • Delete matching records from a large type with a condition, committing every 10,000 records:

ArcadeDB> DELETE FROM LogEntry WHERE createdAt < '2025-01-01' BATCH 10000
  • Delete the profiles with the surname unknown and return the Record ID and name each one had:

ArcadeDB> DELETE FROM Profile WHERE surname = 'unknown' RETURNING @rid, name