Progress
Embedded SQL-92
Guide and Reference


DELETE Rows from a Table

A DELETE statement deletes one or more rows from an existing table, depending on the selection criteria in the WHERE clause.

CAUTION: If you do not specify a WHERE clause, the DELETE statement deletes all rows from the table.
EXAMPLE

The following code fragment shows how to use a DELETE statement to delete a row from the customer table. The program uses the host variable cust_no_v to match a row with cust_no 1005, and deletes the row from the table. If the cust_no column is a primary key or is a candidate key, one row is deleted. If there are multiple rows with cust_no = 1005, multiple rows are deleted:

/* CONNECT to the DEFAULT database */
EXEC SQL CONNECT TO DEFAULT ;

/* Get value for cust_no_v */
cust_no_v = 1005 ;

EXEC SQL
     DELETE
     FROM customer
     WHERE cust_no = :cust_no_v;
 
if (sqlca.sqlcode < 0)
{
     printf ("DELETE statement failed (%ld : %s)\n",
              sqlca.sqlcode, sqlca.sqlerrm);
     EXEC SQL ROLLBACK WORK ;
     EXEC SQL DISCONNECT DEFAULT ;
     exit (1);
}
/* Successful; commit the delete operation.*/ 
EXEC SQL COMMIT WORK ;
 
printf ("Deleted one row \n\n");
 
/* DISCONNECT from the DEFAULT database */
EXEC SQL DISCONNECT DEFAULT ; 

A DELETE operates on more than one row if the WHERE clause matches multiple rows.

EXAMPLE

The following example deletes any rows from the orders table where the value in the order_date column is less than 2/2/1999:

EXEC SQL
     DELETE
     FROM orders
     WHERE order_date < TO_DATE (’02/02/1999’) ; 


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095