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