DML DELETE


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.
Note!   If the DELETE statement executed successfully, SQLERRD(3) is set to the number of rows got deleted.