Delete
Deletes rows from a database table.
Syntax
[repeated] delete from [owner.]tablename [corrname]
[where qual]
[owner.]tablename
Specifies the name of the table from which the row is deleted, and the owner of the table. The table name can be a 4GL name; however, if a 4GL variable is used, an owner cannot be specified, unless the owner name is included as part of the value. In other words, the variable can contain the value owner.tablename.
corrname
Specifies the correlation name of the table
qual
Specifies a logical expression indicating which rows to delete from the table. It cannot include 4GL names. The qualification function is allowed.
Description
The delete statement removes rows from a table based on the qualification in the where clause, if present. Without the qualification, the statement deletes all the rows.
• You can use the qualification function in the where clause.
• You can store the where clause in a variable; however, in this case, you cannot use the qualification function.
• If you use the qualification function, the keyword repeated, if specified, is ignored.
Place a dereferencing colon [:] before the names of fields in the where clause so that the values in the fields are used and are not taken to be column names.
If you want to use the same delete statement frequently, consider using the repeated option for increased efficiency.
Deleting multiple rows from a table based on values from a table field normally involves the use of the unloadtable statement. For more information, see
unloadtable Statement--Loop Through Rows and Execute Statements.
Examples
Delete all rows in the Personnel table containing the employee number (empno) displayed in the current form, using the repeated option, then commit the changes:
repeated delete from personnel
where personnel.empno = :empno;
commit;
Delete all rows in the Personnel table:
delete from personnel;
Delete all rows in the table specified in the tablename field:
delete from :tablename;
Delete rows from a table, where the table name and the where clause are expressed as variables:
whereclause = 'empno = 12';
tablename = 'personnel';
delete from :tablename
where :whereclause;
commit;
Delete rows from a table, where the table name is expressed as a variable and the where clause uses the qualification function:
delete from :tablename
where qualification(empno =:field1,
empname =:field2);