SQL Procedure

DELETE Statement

Removes one or more rows from a table or view that is specified in the FROM clause.

Restriction:	You cannot use DELETE FROM on a table that is accessed by an engine that does not support UPDATE processing.
See:	Combining Two Tables

Syntax

Required Arguments

Details

Deleting Rows through Views

Syntax

DELETE

FROM table-name|sas/access-view|proc-sql-view <AS alias>

<WHERE sql-expression>;

Required Arguments

alias

assigns an alias to table-name, sas/access-view, or proc-sql-view.

sas/access-view

specifies a SAS/ACCESS view that you are deleting rows from.

proc-sql-view

specifies a PROC SQL view that you are deleting rows from. proc-sql-view can be a one-level name, a two-level libref.view name, or a physical pathname that is enclosed in single quotation marks.

sql-expression

See sql-expression

table-name

specifies the table that you are deleting rows from. table-name can be a one-level name, a two-level libref.table name, or a physical pathname that is enclosed in single quotation marks.

CAUTION:

Recursive table references can cause data integrity problems.

Although it is possible to recursively reference the target table of a DELETE statement, doing so can cause data integrity problems and incorrect results. Constructions such as the following should be avoided:

proc sql;
   delete from a
      where var1 > (select min(var2) from a);

Details

Deleting Rows through Views

You can delete one or more rows from a view's underlying table, with some restrictions. See Updating PROC SQL and SAS/ACCESS Views.

CAUTION:

If you omit a WHERE clause, the DELETE statement deletes all of the rows from the specified table or the table that is described by a view. The rows are not actually deleted from the table until it is re-created.