isCOBOL Evolve : Language Reference : Embedded SQL Statements : DELETE
DELETE
The DELETE statement removes rows from a database table.
General format
EXEC SQL [ AT Database ]
 
  [ FOR Iterations ]
 
  DELETE Options [ WHERE { Search-Condition       } ]
                         { CURRENT OF Cursor-Name }
 
                 [ RETURNING Field ... INTO HostVariable ... ]
 
END-EXEC
Syntax rules
1. Iterations can be either a host variable or a numeric literal. It specifies the number of rows to be processed.
2. Options is passed to the driver without further checks. Refer to the database documentation for detailed syntax. Syntax errors, if any, are returned at runtime.
3. Search-Condition is composed of predicates of various kinds, optionally combined using parentheses and logical operators. Its syntax is database dependent, therefore only Host Variables are handled and no further syntax checking is performed. Syntax errors, if any, are returned at runtime.
4. Cursor-Name must be previously defined by a DECLARE statement.
5. Field is a alphanumeric literal.
6. Host-Variable is a host variable.
General rules
1. When Search-Condition is specified, only rows matching it are deleted.
2. The FOR clause limits the number of times the statement is executed when Search-Condition contains array host variables If you omit this clause,it executes the statement once for each component of the smallest array.
3. When Search-Condition is not specified, all rows are deleted.
4. Database identifies the active connection that will execute the query and must be previously defined using a Format 4 DECLARE statement.
5. CURRENT OF allows you to delete the last fetched record in the active cursor.
When this syntax is used
o the statement associated to the cursor should not use "SELECT *" and
o the statement associated to the cursor should query from a single table.
Otherwise a "cursor is not updatable" error may occur.
6. The RETURNING clause allows you to receive the updated fields value into host variables.
Examples
Delete 2 records by specific key
exec sql
   delete from customers where  cust-code = 2 or cust-code = 4
end-exec
Declare, open and fetch a cursor. Delete records where the field cust-name is empty.
exec sql 
   declare cust_cur  cursor for select cust_code, cust_name from customers
end-exec               
 
exec sql 
   open cust_cur
end-exec     
 
perform until 1 = 2
   exec 
      sql fetch next cust_cur into :ws-cust-code, :ws-cust-name
   end-exec
 
   if ws-cust-name = space
      exec sql 
           delete from customers where current of cust_cur
      end-exec
   end-if
 
   if sqlcode = 100
      exit perform
   end-if
 
end-perform
 
exec sql 
   close cust_cur
end-exec