DELETE (SQL)
Synopsis
DELETE [%keyword] [FROM] table-ref [[AS] t-alias]
[FROM [optimize-option] select-table [[AS] t-alias]
{,select-table2 [[AS] t-alias]} ]
[WHERE condition-expression]
DELETE [%keyword] [FROM] table-ref [[AS] t-alias]
[WHERE CURRENT OF cursor]
Arguments
Argument | Description |
---|---|
%keyword | Optional — One or more of the following keyword options, separated by spaces: %NOCHECK, %NOFPLAN, %NOINDEX, %NOJOURN, %NOLOCK, %NOTRIGGER, %PROFILE, %PROFILE_ALL. |
FROM table-ref |
The table from which you are deleting rows. This is not a FROM clause; it is a FROM keyword followed by a single table reference. (The FROM keyword is optional; the table-ref is mandatory.) A table name (or view name) can be qualified (schema.table), or unqualified (table). An unqualified name is matched to its schema using either a schema search path (if provided) or the default schema name. Rather than a table reference, you can specify a view through which table rows can be deleted, or specify a subquery enclosed in parentheses. Unlike the SELECT statement FROM clause, you cannot specify optimize-option keywords here. You cannot specify a table-valued function or JOIN syntax in this argument. |
FROM clause |
Optional — A FROM clause, specified after the table-ref. This FROM can be used to specify a select-table table or tables used to select which rows are to be deleted. Multiple tables can be specified as a comma-separated list or associated with ANSI join keywords. Any combination of tables or views can be specified. If you specify a comma between two select-tables here, InterSystems IRIS performs a CROSS JOIN on the tables and retrieves data from the results table of the JOIN operation. If you specify ANSI join keywords between two select-tables here, InterSystems IRIS performs the specified join operation. For further details, see JOIN. You can optionally specify one or more optimize-option keywords to optimize queryexecution. The available options are: %ALLINDEX, %FIRSTTABLE tablename, %FULL, %INORDER, %IGNOREINDICES, %NOFLATTEN, %NOMERGE, %NOSVSO, %NOTOPOPT, %NOUNIONOROPT, %PARALLEL, and %STARTTABLE. See FROM clause for more details. |
AS t-alias | Optional — An alias for a table or view name. An alias must be a valid identifier. The AS keyword is optional. |
WHERE condition-expression | Optional — Specifies one or more boolean predicates used to limit which rows are to be deleted. You can specify a WHERE clause or a WHERE CURRENT OF clause, but not both. If a WHERE clause (or a WHERE CURRENT OF clause) is not supplied, DELETE removes all the rows from the table. For further details, see WHERE. |
WHERE CURRENT OF cursor | Optional: Embedded SQL only — Specifies that the DELETE operation deletes the record at the current position of cursor. You can specify a WHERE CURRENT OF clause or a WHERE clause, but not both. If a WHERE CURRENT OF clause (or a WHERE clause) is not supplied, DELETE removes all the rows from the table. For further details, see WHERE CURRENT OF. |
Description
The DELETE command removes rows from a table that meet the specified conditions. You can delete rows from a table directly, delete through a view, or delete rows selected using a subquery. Deleting through a view is subject to requirements and restrictions, as described in CREATE VIEW.
The DELETE operation sets the %ROWCOUNT local variable to the number of deleted rows, and the %ROWID local variable to the RowID value of the last row deleted. If no rows are deleted, %ROWCOUNT=0 and %ROWID is undefined or remains set to its previous value.
You must specify a table-ref; the FROM keyword before the table-ref is optional. To delete all rows from a table, you can simply specify:
DELETE FROM tablename
or
DELETE tablename
This deletes all row data from the table, but does not reset the RowID, IDENTITY, stream field OID values, and SERIAL (%Library.Counter) field counters. The TRUNCATE TABLE command both deletes all row data from a table and resets these counters. By default, DELETE FROM tablename pulls delete triggers; you can speciy DELETE %NOTRIGGER FROM tablename to not pull delete triggers. TRUNCATE TABLE does not pull delete triggers.
More commonly, a DELETE specifies the deletion of a specific row (or rows) based on a condition-expression. By default, a DELETE operation goes through all of the rows of a table and deletes all rows that satisfy the condition-expression. If no rows satisfy the condition-expression, DELETE completes successfully and sets SQLCODE=100 (No more data).
You can specify a WHERE clause or a WHERE CURRENT OF clause (but not both). If the WHERE CURRENT OF clause is used, the DELETE operation deletes the record at the current position of the cursor. For an example of DELETE using WHERE CURRENT OF, see “Embedded SQL and Dynamic SQL Examples” below. For details on positioned operations, see WHERE CURRENT OF.
By default, DELETE is an all-or-nothing event: either all specified rows are deleted completely, or no deletion is performed. InterSystems IRIS sets the status variable SQLCODE, indicating the success or failure of the DELETE.
To delete a row from a table:
-
The table must exist in the current (or specified) namespace. If the specified table cannot be located, InterSystems IRIS issues an SQLCODE -30 error.
-
The user must have DELETE privilege on the specified table. If the user is the Owner (creator) of the table, the user is automatically granted DELETE privilege for that table. Otherwise, the user must be granted DELETE privilege for the table. Failing to do so results in an SQLCODE –99 error with the %msg User 'name' is not privileged for the operation. You can determine if the current user has DELETE privilege by invoking the %CHECKPRIV command. You can use the GRANT command to assign DELETE privilege to a specified table. For further details, refer to Privileges.
-
The table cannot be locked IN EXCLUSIVE MODE by another process. Attempting to delete a row from a locked table results in an SQLCODE -110 error, with a %msg such as the following: Unable to acquire lock for DELETE of table 'Sample.Person' on row with RowID = '10'. Note that an SQLCODE -110 error occurs only when the DELETE statement locates the first record to be deleted, then cannot lock it within the timeout period.
-
If the DELETE command’s WHERE clause specifies a non-existent field, an SQLCODE -29 is issued. To list all of the field names defined for a specified table, refer to Column Names and Numbers. If the field exists but none of the field values fulfill the DELETE command’s WHERE clause, no rows are affected and SQLCODE 100 (end of data) is issued.
-
The table cannot be defined as READONLY. Attempting to compile a DELETE that references a read-only table results in an SQLCODE -115 error. Note that this error is now issued at compile time, rather than only occurring at execution time. See the description of READONLY objects in Other Options for Persistent Classes.
-
If deleting through a view, the view cannot be defined as WITH READ ONLY. Attempting to do so results in an SQLCODE -35 error. If the view is based on a sharded table, you cannot DELETE through a view defined WITH CHECK OPTION. Attempting to do so results in an SQLCODE -35 with the %msg INSERT/UPDATE/DELETE not allowed for view (sample.myview) based on sharded table with check option conditions. See the CREATE VIEW command for further details. Similarly, if you are attempting to delete through a subquery, the subquery must be updateable; for example, the following subquery results in an SQLCODE -35 error: DELETE FROM (SELECT COUNT(*) FROM Sample.Person) AS x.
-
The row to delete must exist. Usually, attempting to delete a nonexistent row results in an SQLCODE 100 (No more data) because the specified row could not be located. However, in rare cases, DELETE with %NOLOCK locates a row to be deleted, but then the row is immediately deleted by another process; this situation results in an SQLCODE -106 error. The %msg for this error lists the table name and the RowID.
-
All of the rows specified for deletion must be available for deletion. By default, if one or more rows cannot be deleted the DELETE operation fails and no rows are deleted. If a row to be deleted has been locked by another concurrent process, DELETE issues an SQLCODE -110 error. If deleting one of the specified rows would violate foreign key referential integrity (and %NOCHECK is not specified), the DELETE issues an SQLCODE -124 error. This default behavior is modifiable, as described below.
-
Certain %SYS namespace system–supplied facilities are protected against deletion. For example, DELETE FROM Security.Users cannot be used to delete _SYSTEM, _PUBLIC or UnknownUser. Attempting to do so results in an SQLCODE -134 error.
FROM Syntax
A DELETE command can contain two FROM keywords that specify tables. These two uses of FROM are fundamentally different:
-
FROM before table-ref specifies the table (or view) from which rows are to be deleted. It is a FROM keyword, not a FROM clause. Only one table may be specified. No join syntax or optimize-option keywords may be specified. The FROM keyword itself is optional; the table-ref is required.
-
FROM after table-ref is an optional FROM clause that can be used to determine which rows should be deleted. It may specify one or more than one tables. It supports all of the FROM clause syntax available to a SELECT statement, including join syntax and optimize-option keywords. This FROM clause is commonly (but not always) used with a WHERE clause.
Thus any of the following are valid syntactical forms:
DELETE FROM table WHERE ... DELETE table WHERE ... DELETE
FROM table FROM table2 WHERE ... DELETE table FROM table2 WHERE ...
This syntax supports complex selection criteria in a manner compatible with Transact-SQL.
The following example shows how the two FROM keywords might be used. It deletes those records from the Employees table where the same EmpId is also found in the Retirees table:
DELETE FROM Employees AS Emp
FROM Retirees AS Rt
WHERE Emp.EmpId = Rt.EmpId
If the two FROM keywords make reference to the same table, these references may either be to the same table, or to a join of two instances of the table. This depends on how table aliases are used:
-
If neither table reference has an alias, both reference the same table:
DELETE FROM table1 FROM table1,table2 /* join of 2 tables */
-
If both table references have the same alias, both reference the same table:
DELETE FROM table1 AS x FROM table1 AS x,table2 /* join of 2 tables */
-
If both table references have aliases, and the aliases are different, InterSystems IRIS performs a join of two instances of the table:
DELETE FROM table1 AS x FROM table1 AS y,table2 /* join of 3 tables */
-
If the first table reference has an alias, and the second does not, InterSystems IRIS performs a join of two instances of the table:
DELETE FROM table1 AS x FROM table1,table2 /* join of 3 tables */
-
If the first table reference does not have an alias, and the second has a single reference to the table with an alias, both reference the same table, and this table has the specified alias:
DELETE FROM table1 FROM table1 AS x,table2 /* join of 2 tables */
-
If the first table reference does not have an alias, and the second has more than one reference to the table, InterSystems IRIS considers each aliased instance a separate table and performs a join on these tables:
DELETE FROM table1 FROM table1,table1 AS x,table2 /* join of 3 tables */ DELETE FROM table1 FROM table1 AS x,table1 AS y,table2 /* join of 4 tables */
%Keyword Options
Specifying %keyword argument(s) restricts processing as follows:
-
%NOCHECK — suppress referential integrity checking for foreign keys that reference the rows being deleted. The user must have the corresponding %NOCHECK administrative privilege for the current namespace to apply this restriction. Failing to do so results in an SQLCODE –99 error with the %msg User 'name' does not have %NOCHECK privileges.
-
%NOFPLAN — the frozen plan (if any) is ignored for this operation; the operation generates a new query plan. The frozen plan is retained, but not used. For further details, refer to Frozen Plans.
-
%NOINDEX — suppresses deleting index entries in all indexes for the rows being deleted. This should be used with extreme caution, because it leaves orphaned values in the table indexes. The user must have the corresponding %NOINDEX administrative privilege for the current namespace to apply this restriction. Failing to do so results in an SQLCODE –99 error with the %msg User 'name' does not have %NOINDEX privileges.
-
%NOJOURN — suppress journaling and disable transactions for the duration of the delete operation. None of the changes made in any of the rows are journaled, including any triggers pulled. If you perform a ROLLBACK after a statement with %NOJOURN, the changes made by the statement will not be rolled back. The user must have the corresponding %NOJOURN administrative privilege for the current namespace to apply this restriction. Failing to do so results in an SQLCODE –99 error with the %msg User 'name' does not have %NOJOURN privileges.
-
%NOLOCK — suppress row locking of the row being deleted. This should only be used when a single user/process is updating the database. The user must have the corresponding %NOLOCK administrative privilege for the current namespace to apply this restriction. Failing to do so results in an SQLCODE –99 error with the %msg User 'name' does not have %NOLOCK privileges.
-
%NOTRIGGER — suppress the pulling of base table triggers that are otherwise pulled during DELETE processing. The user must have the corresponding %NOTRIGGER administrative privilege for the current namespace to apply this restriction. Failing to do so results in an SQLCODE –99 error with the %msg User 'name' does not have %NOTRIGGER privileges.
-
%PROFILE or %PROFILE_ALL — if one of these keyword directives is specified, SQLStats collecting code is generated. This is the same code that would be generated with PTools turned ON. The difference is that SQLStats collecting code is only generated for this specific statement. All other SQL statements within the routine/class being compiled will generate code as if PTools is turned OFF. This enables the user to profile/inspect specific problem SQL statements within an application without collecting irrelevant statistics for SQL statements that are not being investigated. For further details, refer to SQL Runtime Statistics.
%PROFILE collects SQLStats for the main query module. %PROFILE_ALL collects SQLStats for the main query module and all of its subquery modules.
You can specify multiple %keyword arguments in any order. Multiple arguments are separated by spaces.
If you specify a %keyword argument when deleting a parent record, the same %keyword argument will be applied when deleting the corresponding child records.
Referential Integrity
If you do not specify %NOCHECK, InterSystems IRIS uses the system-wide configuration setting to determine whether to perform foreign key referential integrity checking; the default is to perform foreign key referential integrity checking. You can set this default system-wide, as described in Foreign Key Referential Integrity Checking. To determine the current system-wide setting, call $SYSTEM.SQL.CurrentSettings()Opens in a new tab.
During a DELETE operation, for every foreign key reference a shared lock is acquired on the corresponding row in the referenced table. This row is locked until the end of the transaction. This ensures that the referenced row is not changed before a potential rollback of the DELETE.
If a series of foreign key references are defined as CASCADE, a DELETE operation could potentially result in a circular reference. InterSystems IRIS prevents DELETE with CASCADE referential action from performing a circular reference loop recursion. InterSystems IRIS ends the cascade sequence when it returns to the original table.
If a DELETE operation with %NOLOCK is performed on a foreign key field defined with CASCADE, SET NULL, or SET DEFAULT, the corresponding referential action changing the foreign key table is also performed with %NOLOCK.
Atomicity
By default, DELETE, UPDATE, INSERT, and TRUNCATE TABLE are atomic operations. A DELETE either completes successfully or the whole operation is rolled back. If any of the specified rows cannot be deleted, none of the specified rows are deleted and the database reverts to its state before issuing the DELETE.
You can modify this default for the current process within SQL by invoking SET TRANSACTION %COMMITMODE. You can modify this default for the current process in ObjectScript by invoking the SetOption()Opens in a new tab method, as follows SET status=$SYSTEM.SQL.Util.SetOption("AutoCommit",intval,.oldval). The following intval integer options are available:
-
1 or IMPLICIT (autocommit on) — The default behavior, as described above. Each DELETE constitutes a separate transaction.
-
2 or EXPLICIT (autocommit off) — If no transaction is in progress, a DELETE automatically initiates a transaction, but you must explicitly COMMIT or ROLLBACK to end the transaction. In EXPLICIT mode the number of database operations per transaction is user-defined.
-
0 or NONE (no auto transaction) — No transaction is initiated when you invoke DELETE. A failed DELETE operation can leave the database in an inconsistent state, with some of the specified rows deleted and some not deleted. To provide transaction support in this mode you must use START TRANSACTION to initiate the transaction and COMMIT or ROLLBACK to end the transaction.
A sharded table is always in no auto transaction mode, which means all inserts, updates, and deletes to sharded tables are performed outside the scope of a transaction.
You can determine the atomicity setting for the current process using the GetOption("AutoCommit")Opens in a new tab method, as shown in the following ObjectScript example:
SET stat=$SYSTEM.SQL.Util.SetOption("AutoCommit",$RANDOM(3),.oldval)
IF stat'=1 {WRITE "SetOption failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET x=$SYSTEM.SQL.Util.GetOption("AutoCommit")
IF x=1 {
WRITE "Default atomicity behavior",!
WRITE "automatic commit or rollback" }
ELSEIF x=0 {
WRITE "No transaction initiated, no atomicity:",!
WRITE "failed DELETE can leave database inconsistent",!
WRITE "rollback is not supported" }
ELSE { WRITE "Explicit commit or rollback required" }
Transaction Locking
If you do not specify %NOLOCK, the system automatically performs standard record locking on INSERT, UPDATE, and DELETE operations. Each affected record (row) is locked for the duration of the current transaction.
The default lock threshold is 1000 locks per table, which means if you delete more than 1000 records from a table during a transaction, the lock threshold is reached and InterSystems IRIS automatically escalates the locking level from record locks to a table lock. This permits large-scale deletes during a transaction without overflowing the lock table.
InterSystems IRIS applies one of the two following lock escalation strategies:
-
“E”-type lock escalation: InterSystems IRIS uses this type of lock escalation if the following are true: (1) the class uses %Storage.Persistent (you can determine this from the Catalog Details in the Management Portal SQL schema display). (2) the class either does not specify an IDKey index, or specifies a single-property IDKey index. “E”-type lock escalation is described in the LOCK command.
-
Traditional SQL lock escalation: The most likely reason why a class would not use “E”-type lock escalation is the presence of a multi-property IDKey index. In this case, each %Save increments the lock counter. This means if you do 1001 saves of a single object within a transaction, InterSystems IRIS will attempt to escalate the lock.
For both lock escalation strategies, you can determine the current system-wide lock threshold value using the $SYSTEM.SQL.Util.GetOption("LockThreshold")Opens in a new tab method. The default is 1000. This system-wide lock threshold value is configurable:
-
Using the $SYSTEM.SQL.Util.SetOption("LockThreshold")Opens in a new tab method.
-
Using the Management Portal: select System Administration, Configuration, SQL and Object Settings, SQL. View and edit the current setting of Lock escalation threshold. The default is 1000 locks. If you change this setting, any new process started after changing it will have the new setting.
You must have USE permission on the %Admin Manage Resource to change the lock threshold. InterSystems IRIS immediately applies any change made to the lock threshold value to all current processes.
One potential consequence of automatic lock escalation is a deadlock situation that might occur when an attempt to escalate to a table lock conflicts with another process holding a record lock in that table. There are several possible strategies to avoid this: (1) increase the lock escalation threshold so that lock escalation is unlikely to occur within a transaction. (2) substantially lower the lock escalation threshold so that lock escalation occurs almost immediately, thus decreasing the opportunity for other processes to lock a record in the same table. (3) apply a table lock for the duration of the transaction and do not perform record locks. This can be done at the start of the transaction by specifying LOCK TABLE, then UNLOCK TABLE (without the IMMEDIATE keyword, so that the table lock persists until the end of the transaction), then perform deletes with the %NOLOCK option.
Automatic lock escalation is intended to prevent overflow of the lock table. However, if you perform such a large number of deletes that a <LOCKTABLEFULL> error occurs, DELETE issues an SQLCODE -110 error.
For further details on transaction locking refer to Transaction Processing.
Examples
The following examples both delete all rows from the TempEmployees table. Note that the FROM keyword is optional:
DELETE FROM TempEmployees
DELETE TempEmployees
The following example deletes employee number 234 from the Employees table:
DELETE
FROM Employees
WHERE EmpId = 234
The following example deletes all rows from the ActiveEmployees table in which the CurStatus column is set to "Retired":
DELETE FROM ActiveEmployees
WHERE CurStatus = 'Retired'
The following example deletes rows using a subquery:
DELETE FROM (SELECT Name,Age FROM Sample.Person WHERE Age > 65)
Table Deletion Example
The following example demonstrates the task of deleting rows from a newly-created table and then subsequetly deleting the table itself.
The first command in this example creates a table named SQLUser.WordPairs with three columns.
CREATE TABLE SQLUser.WordPairs (
Lang CHAR(2) NOT NULL,
Firstword CHAR(30),
Lastword CHAR(30))
The next few commands insert six records into the table.
INSERT INTO WordPairs (Lang,Firstword,Lastword) VALUES
('En','hello','goodbye')
INSERT INTO WordPairs (Lang,Firstword,Lastword) VALUES
('Fr','bonjour','au revoir')
INSERT INTO WordPairs (Lang,Firstword,Lastword) VALUES
('It','pronto','ciao')
INSERT INTO WordPairs (Lang,Firstword,Lastword) VALUES
('Fr','oui','non')
INSERT INTO WordPairs (Lang,Firstword,Lastword) VALUES
('En','howdy','see ya')
INSERT INTO WordPairs (Lang,Firstword,Lastword) VALUES
('Es','hola','adios')
The following commands delete all English records using cursor-based Embedded SQL.
#sqlcompile path=Sample
NEW %ROWCOUNT,%ROWID
&sql(DECLARE WPCursor CURSOR FOR
SELECT Lang FROM WordPairs
WHERE Lang='En')
&sql(OPEN WPCursor)
QUIT:(SQLCODE'=0)
FOR { &sql(FETCH WPCursor)
QUIT:SQLCODE
&sql(DELETE FROM WordPairs
WHERE CURRENT OF WPCursor)
IF SQLCODE=0 {
WRITE !,"Delete succeeded"
WRITE !,"Row count=",%ROWCOUNT," RowID=",%ROWID }
ELSE {
WRITE !,"Delete failed, SQLCODE=",SQLCODE }
}
&sql(CLOSE WPCursor)
This command then deletes all French records.
DELETE FROM WordPairs WHERE Lang='Fr'
The final two commands display the remaining records in the table and delete the table.
SELECT %ID,* FROM SQLUser.WordPairs
DROP TABLE SQLUser.WordPairs