5.5. Deleting Rows in a Table

In the world of data management, the ability to delete unwanted rows of data from a table is as important as being able to populate a table with data. In fact, data management activities would be severely hampered without the ability to delete rows of data. The DELETE statement and an optional WHERE clause can remove one or more unwanted rows from a table, depending on what is specified in the WHERE clause.

5.5.1. Deleting a Single Row in a Table

The DELETE statement can be specified to remove a single row of data by constructing an explicit WHERE clause on a unique value. The construction of a WHERE clause to satisfy this form of row deletion may require a complex logic construct. So be sure to test the expression thoroughly before applying it to the table to determine whether it performs as expected. The following example illustrates the removal of a single customer in the CUSTOMERS table by specifying the customer’s name (CUSTNAME) in the WHERE clause.

SQL Code

PROC SQL;
  DELETE FROM CUSTOMERS2
    WHERE UPCASE(CUSTNAME) = "LAUGHLER";
QUIT;

SAS Log Results

     PROC SQL;
       DELETE FROM CUSTOMERS2
         WHERE UPCASE(CUSTNAME) = "LAUGHLER";
NOTE: 1 row was deleted from WORK.CUSTOMERS2.
     QUIT;
NOTE: PROCEDURE SQL used:
      real time           0.37 seconds

5.5.2. Deleting More Than One Row in a Table

Frequently, a row deletion affects more than a single row in a table. In these cases a WHERE clause references a value occurring multiple times. The following example illustrates the removal of a single customer in the PRODUCTS table by specifying the product type (PRODTYPE) in the WHERE clause.

SQL Code

PROC SQL;
  DELETE FROM PRODUCTS
    WHERE UPCASE(PRODTYPE) = 'PHONE';
QUIT;

SAS Log Results

     PROC SQL;
       DELETE FROM PRODUCTS
         WHERE UPCASE(PRODTYPE) = 'PHONE';
NOTE: 3 rows were deleted from WORK.PRODUCTS.
     QUIT;
NOTE: PROCEDURE SQL used:
      real time           0.05 seconds

5.5.3. Deleting All Rows in a Table

SQL provides a simple way to delete all rows in a table. The following example shows that all rows in the CUSTOMERS table can be removed when the WHERE clause is omitted. Use care when using this form of the DELETE statement because every row in the table is automatically deleted.

SQL Code

PROC SQL;
  DELETE FROM CUSTOMERS;
QUIT;

SAS Log Results

     PROC SQL;
       DELETE FROM CUSTOMERS;
NOTE: 28 rows were deleted from WORK.CUSTOMERS.
     QUIT;
NOTE: PROCEDURE SQL used:
      real time           0.00 seconds