Removes rows specified by search conditions.
exec sql [at connection_name] delete table_name_1 [from table_name_n [, table_name_n]…] [where search_conditions] end-exec
The name of the table from which this delete statement deletes rows.
The name of a table to be joined with table_name_1 to determine which rows of table_name_1 will be deleted. The delete statement does not delete rows from table_name_n.
Specifies which rows will be deleted. If you omit the where clause, the delete statement deletes all rows of table_name_1.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 AU-FNAME PIC X(30).
01 AU-LNAME PIC X(30).
01 AU-ID PIC X(11).
01 TITLE-ID PIC X(6).
EXEC SQL END DECLARE SECTION END-EXEC.
...
EXEC SQL WHENEVER SQLERROR PERFORM ROLLBACK-PARA.
EXEC SQL USE pubs2 END-EXEC.
DISPLAY "AUTHOR FIRST NAME ? "
ACCEPT AU-FNAME.
DISPLAY "AUTHOR LAST NAME ? "
ACCEPT AU-LNAME.
EXEC SQL SELECT au_id FROM authors INTO :AU-ID
WHERE au_fname = :AU-FNAME
AND au_lname = :AU-LNAME END-EXEC.
EXEC SQL BEGIN TRANSACTION END-EXEC.
* Delete matching records from the 'au_pix' table.
EXEC SQL DELETE au_pix WHERE au_id = :AU-ID END-EXEC.
* Delete matching records from the 'blurbs' table.
EXEC SQL DELETE blurbs WHERE au_id = :AU-ID END-EXEC.
* Delete matching records from the titleauthor table. Since
* we can't have titles associated with this author in other
* related tables, we delete those records too.
EXEC SQL DECLARE selcursor CURSOR FOR
SELECT title_id FROM titleauthor
WHERE au_id = :AU-ID END-EXEC.
EXEC SQL OPEN selcursor END-EXEC.
PERFORM FETCH-DEL-LOOP UNTIL SQLCODE = 100.
EXEC SQL CLOSE selcursor END-EXEC.
EXEC SQL DEALLOCATE CURSOR selcursor END-EXEC.
* Delete matching records from the 'authors' table.
EXEC SQL DELETE authors WHERE au_id = :AU-ID END-EXEC.
* Commit all the transactions to the database.
EXEC SQL COMMIT TRANSACTION END-EXEC.
...
FETCH-DEL-LOOP.
EXEC SQL FETCH selcursor INTO :TITLE-ID END-EXEC
IF SQLCODE <> 100
EXEC SQL DELETE salesdetail WHERE title_id = :TITLE-ID END-EXEC
EXEC SQL DELETE roysched WHERE title_id = :TITLE-ID END-EXEC
EXEC SQL DELETE titles WHERE title_id = :TITLE-ID END-EXEC
EXEC SQL DELETE titleauthor WHERE CURRENT OF selcursor END-EXEC
END-IF.
END-FETCH-LOOP.
* Rollback the transacion in case of errors.
ROLLBACK-PARA.
DISPLAY "ERROR! ROLLING BACK TRANSACTION!"
DISPLAY "Error code is " SQLCODE.
DISPLAY "Error message is " SQLERRMC.
EXEC SQL ROLLBACK TRANSACTION END-EXEC.
...
This reference page describes mainly aspects of the Transact-SQL delete statement that differ when used with Embedded SQL. See the Adaptive Server Enterprise Reference Manual for more information about the delete statement.
If you need to remove rows specified by the current position of a cursor pointer, use the delete (positioned cursor) statement.
close, declare cursor, fetch, open, update
