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

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 in Using InterSystems SQL.

• 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 in the “Defining Tables” chapter of Using InterSystems SQL. 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 the Other Options for Persistent Classes chapter of Defining and Using 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 in SQL Optimization Guide.

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

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" }

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

See Also

• FROM

• TRUNCATE TABLE

• INSERT UPDATE

• CREATE VIEW

• WHERE

• WHERE CURRENT OF

• “Modifying the Database” chapter

• “Defining Tables” chapter

• “Defining Views” chapter of Using InterSystems SQL

• Transaction Processing in the “Modifying the Database” chapter of Using InterSystems SQL

• SQL and Object Settings Pages listed in System Administration Guide.

• SQLCODE error messages listed in the InterSystems IRIS Error Reference