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:
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 in SQL Optimization Guide.
-
%NOINDEX — suppresses deleting index entries
in all indices for the rows being deleted. This should be used with
extreme caution, because it leaves orphaned values in the table indices.
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 in the InterSystems SQL Optimization Guide.
%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 in the ObjectScript Reference.
-
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 in the
“Modifying the Database” chapter of Using InterSystems
SQL.
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
