Caché SQL Reference
DELETE
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   
Search:    

Removes rows from a table.
Synopsis
DELETE [%NOFPLAN] [restriction] [FROM] table-ref [[AS] t-alias]
     [FROM select-table1 [[AS] t-alias]
              {,select-table2 [[AS] t-alias]} ]
     [WHERE condition-expression]
DELETE [restriction] [FROM] table-ref [[AS] t-alias]
     [WHERE CURRENT OF cursor]
Arguments
%NOFPLAN Optional — The %NOFPLAN keyword specifies that Caché will ignore the frozen plan (if any) for this operation and generate a new query plan. The frozen plan is retained, but not used. For further details, refer to Frozen Plans in Caché SQL Optimization Guide.
restriction Optional — One or more of the following keywords, separated by spaces: %NOLOCK, %NOCHECK, %NOINDEX, %NOTRIGGER.
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.) 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, Caché 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, Caché performs the specified join operation. For further details, refer to the JOIN page of this manual.
You can optionally specify one or more optimize-option keywords to optimize query execution. The available options are: %ALLINDEX, %FIRSTTABLE tablename, %FULL, %INORDER, %IGNOREINDICES, %NOFLATTEN, %NOMERGE, %NOSVSO, %NOTOPOPT, %NOUNIONOROPT, 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 Row ID of the last row deleted.
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, and %Counter 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 specify 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. Caché sets the status variable SQLCODE, indicating the success or failure of the DELETE.
To delete a row from a table:
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 SetAutoCommit() method. The following options are available:
You can determine the atomicity setting for the current process using the GetAutoCommit() method, as shown in the following ObjectScript example:
  DO $SYSTEM.SQL.SetAutoCommit($RANDOM(3))
  SET x=$SYSTEM.SQL.GetAutoCommit()
  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" }
 
FROM Syntax
A DELETE command can contain two FROM keywords that specify tables. These two uses of FROM are fundamentally different:
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:
Restriction Arguments
To use a restriction argument, you must have the corresponding admin-privilege for the current namespace. Refer to GRANT for further details.
Specifying restriction argument(s) restricts processing as follows:
You can specify multiple restriction arguments in any order. Multiple arguments are separated by spaces.
If you specify a restriction argument when deleting a parent record, the same restriction argument will be applied when deleting the corresponding child records.
Referential Integrity
If you do not specify %NOCHECK, Caché uses the system configuration setting to determine whether to perform foreign key referential integrity checking. You can set the system default as follows:
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. Caché prevents DELETE with CASCADE referential action from performing a circular reference loop recursion. Caché 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.
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. This means that if you delete more than 1000 records from a table during a transaction, the lock threshold is reached and Caché 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.
Caché applies one of the two following lock escalation strategies:
For both lock escalation strategies, you can determine the current system-wide lock threshold value using the $SYSTEM.SQL.GetLockThreshold() method. The default is 1000. This system-wide lock threshold value is configurable:
You must have USE permission on the %Admin Manage Resource to change the lock threshold. Caché immediately applies any change made to the lock threshold value to all current processes.
On 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 Caché 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)
Embedded SQL and Dynamic SQL Examples
In the following set of program examples, the first program creates a table named SQLUser.WordPairs with three columns. The next program inserts six records. Subsequent programs delete all English records using cursor-based Embedded SQL, and delete all French records using Dynamic SQL. The final program displays the remaining records, then deletes the table.
CreateTable
   ZNSPACE "Samples"
   &sql(CREATE TABLE SQLUser.WordPairs (
        Lang        CHAR(2) NOT NULL,
        Firstword   CHAR(30),
        Lastword    CHAR(30) )
       )
  IF SQLCODE=0 {
    WRITE !,"Table created" }
  ELSEIF SQLCODE=-201 {WRITE !,"Table already exists"  QUIT}
  ELSE {
    WRITE !,"CREATE TABLE failed. SQLCODE=",SQLCODE }
 
InsertSixRecords
  #SQLCompile Path=Cinema,Sample
  ZNSPACE "Samples"
  &sql(INSERT INTO WordPairs (Lang,Firstword,Lastword) VALUES 
   ('En','hello','goodbye'))
  IF SQLCODE = 0 { WRITE !,"1st record inserted" }
  ELSE { WRITE !,"Insert failed, SQLCODE=",SQLCODE
         QUIT}
  &sql(INSERT INTO WordPairs (Lang,Firstword,Lastword) VALUES 
   ('Fr','bonjour','au revoir'))
  IF SQLCODE = 0 { WRITE !,"2nd record inserted" }
  ELSE { WRITE !,"Insert failed, SQLCODE=",SQLCODE  QUIT}
  &sql(INSERT INTO WordPairs (Lang,Firstword,Lastword) VALUES 
   ('It','pronto','ciao'))
  IF SQLCODE = 0 { WRITE !,"3rd record inserted" }
  ELSE { WRITE !,"Insert failed, SQLCODE=",SQLCODE  QUIT}
  &sql(INSERT INTO WordPairs (Lang,Firstword,Lastword) VALUES 
   ('Fr','oui','non'))
  IF SQLCODE = 0 { WRITE !,"4th record inserted" }
  ELSE { WRITE !,"Insert failed, SQLCODE=",SQLCODE  QUIT}
  &sql(INSERT INTO WordPairs (Lang,Firstword,Lastword) VALUES 
   ('En','howdy','see ya'))
  IF SQLCODE = 0 { WRITE !,"5th record inserted" }
  ELSE { WRITE !,"Insert failed, SQLCODE=",SQLCODE  QUIT}
  &sql(INSERT INTO WordPairs (Lang,Firstword,Lastword) VALUES 
   ('Es','hola','adios'))
  IF SQLCODE = 0 { WRITE !,"6th record inserted",!!
     SET myquery = "SELECT %ID,* FROM SQLUser.WordPairs"
     SET tStatement = ##class(%SQL.Statement).%New()
     SET qStatus = tStatement.%Prepare(myquery)
       IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
     SET rset = tStatement.%Execute()
     DO rset.%Display()
     WRITE !,"End of data" }
  ELSE { WRITE !,"Insert failed, SQLCODE=",SQLCODE }
 
EmbeddedSQLDeleteEnglish
  #SQLCompile Path=Sample
  NEW SQLCODE,%ROWCOUNT,%ROWID
  &sql(DECLARE WPCursor CURSOR FOR 
        SELECT Lang FROM WordPairs
        WHERE Lang='En')
   &sql(OPEN WPCursor)
   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)
 
DynamicSQLDeleteFrench
  SET sqltext = "DELETE FROM WordPairs WHERE Lang=?"
  SET tStatement = ##class(%SQL.Statement).%New(0,"Sample")
  SET qStatus = tStatement.%Prepare(sqltext)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rtn = tStatement.%Execute("Fr")
  IF rtn.%SQLCODE=0 {
    WRITE !,"Delete succeeded"
    WRITE !,"Row count=",rtn.%ROWCOUNT," RowID of last record=",rtn.%ROWID }
  ELSE {
    WRITE !,"Delete failed, SQLCODE=",rtn.%SQLCODE }
 
DisplayAndDeleteTable
  ZNSPACE "Samples"
  SET myquery = "SELECT %ID,* FROM SQLUser.WordPairs"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()
  WRITE !,"End of data"
  &sql(DROP TABLE SQLUser.WordPairs)
   IF SQLCODE=0 {
    WRITE !!,"Table deleted"
    QUIT }
  ELSE {
    WRITE !,"Table delete failed, SQLCODE=",SQLCODE }
 
See Also