DML DELETE
Summary
DELETE statement is used to delete the row/rows from the table or view. DELETE statement can delete more than one row/rows from the table/view based on the condition specified in WHERE clause.
Deleting a row from VIEW can delete the row from the base table as well. User should have DELETE privilege to perform DELETE operation on table or view. Once the DELETE executed successfully, the rows updated count will be stored in SQLERRD (3).
Syntax -
EXEC SQL
DELETE FROM table-name
WHERE conditions
END-EXEC.
If the DELETE has any error during the deletion process, SQLCODE and SQLSTATE will have the error details. If no rows existed in the table which are satisfies where condition, then the DELETE statement will return SQLCODE +100 and delete will be skipped. If the WHERE clause ignored in the DELETE, it will delete all the rows of the table.
How to use DELETE statement in the Program:
Deleting data from table in the program can be done similarly how is done at the outside. The steeps to delete the data in the program is:
- Include the host variables copybook or declare the host variables in working storage section.
- Fill the data to the HOST-variables which are used in WHERE condition.
- Execute the DELETE query to delete the data like below.
Syntax -
EXEC SQL
DELETE FROM Table-name
WHERE Column-1 = :Host-variable-1,
Column-2 = :Host-variable-2,
…,
Column-n = :Host-variable-n)
END-EXEC.