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 FROM <Type>|BUCKET:<bucket>|INDEX:<index> [RETURN <returning>]
  [WHERE <Condition>*] [LIMIT <MaxRecords>] [BATCH <batch-size>]
  [TIMEOUT <MilliSeconds>] [UNSAFE]

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

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