Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
The ADO, OLE DB, and ODBC APIs support deleting the current row on which an application is positioned in a result set. The application executes a statement, and then fetches rows from the result set. After an application has fetched the row, it can use the following functions or methods to delete the row:
- ADO applications use the Delete method of the Recordset object.
- OLE DB applications use the DeleteRows method of the IRowsetChange interface.
- ODBC applications use the SQLSetPos function with the SQL_DELETE option.
- DB-library applications use dbcursor to perform a CRS_DELETE operation.
Transact-SQL scripts, stored procedures, and triggers can use the WHERE CURRENT OF clause on a DELETE statement to delete the cursor row on which they currently are positioned. The following example deletes a single row from the EmployeePayHistory table using a cursor named complex_cursor. DELETE affects only the single row currently fetched from the cursor.
USE AdventureWorks;
GO
DECLARE complex_cursor CURSOR FOR
SELECT a.EmployeeID
FROM HumanResources.EmployeePayHistory AS a
WHERE RateChangeDate <>
(SELECT MAX(RateChangeDate)
FROM HumanResources.EmployeePayHistory AS b
WHERE a.EmployeeID = b.EmployeeID) ;
OPEN complex_cursor;
FETCH FROM complex_cursor;
DELETE FROM HumanResources.EmployeePayHistory
WHERE CURRENT OF complex_cursor;
CLOSE complex_cursor;
DEALLOCATE complex_cursor;
GO
See Also
Concepts
Deleting Rows by Using DELETE
Deleting All Rows by Using TRUNCATE TABLE
Limiting Deleted Rows by Using TOP