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.
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 |
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 |
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;
PROC SQL; DELETE FROM CUSTOMERS; NOTE: 28 rows were deleted from WORK.CUSTOMERS. QUIT; NOTE: PROCEDURE SQL used: real time 0.00 seconds |