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>]
-
VERTEXDeletes only vertices, each with its edges. A target type that is not a vertex type, or anINDEX: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 aBATCHalready committed. Without it,DELETE FROMdeletes 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.) -
RETURNDefines what values the database returns. It takes one of the following values:-
COUNTReturns the number of deleted records. This is the default option. -
BEFOREReturns the number of records before the removal.
-
-
WHEREFilters to the records you want to delete. -
LIMITDefines the maximum number of records to delete. -
BATCHDefines 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. -
TIMEOUTDefines the time period to allow the operation to run, before it times out. -
UNSAFEno use, is kept only for compatibility with OrientDB SQL and it could be removed in the future versions of the SQL language. -
RETURNINGPostgreSQL’s trailing form: returns the deleted records as they were before the delete, projected by<projection>(RETURNING *for all their properties), whereRETURN BEFOREreturns each whole record.RETURNandRETURNINGcannot be combined in the same statement. (Since v26.12.1, issue #11392.)
|
On a TIMESERIES type (since 26.11.1) |
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
unknownand return the Record ID and name each one had:
ArcadeDB> DELETE FROM Profile WHERE surname = 'unknown' RETURNING @rid, name