Reference Manual
   CHAPTER 9. SQL Statements     

DELETE statement

Function 

To delete rows from the database.

Syntax 

DELETE [FROM] [ owner.]table-name
... [FROM table-list]
... [WHERE search-condition]

Permissions 

Must have DELETE permission on the table.

Side effects 

None.

See also 

TRUNCATE TABLE statement

INSERT statement

INPUT statement

FROM clause

Description 

The DELETE statement deletes all the rows from the named table that satisfy the search condition. If no WHERE clause is specified, all rows from the named table are deleted.

The DELETE statement can be used on views, provided the SELECT statement defining the view has only one table in the FROM clause and does not contain a GROUP BY clause, an aggregate function, or involve a UNION operation.

     For a full description of the FROM clause and joins, see FROM clause.

The optional second FROM clause in the DELETE statement allows rows to be deleted based on joins. If the second FROM clause is present, the WHERE clause qualifies the rows of this second FROM clause. Rows are deleted from the table name given in the first FROM clause.

Correlation name resolution 

The following statement illustrates a potential ambiguity in table names in DELETE statements with two FROM clauses that use correlation names:

DELETE
FROM table_1
FROM table_1 AS alias_1, table_2 AS alias_2
WHERE ...

The table table_1 is identified without a correlation name in the first FROM clause, but with a correlation name in the second FROM clause. In this case, table_1 in the first clause is identified with alias_1 in the second clause—there is only one instance of table_1 in this statement.

This is an exception to the general rule that where a table is identified with a correlation name and without a correlation name in the same statement, two instances of the table are considered.

Consider the following example:

DELETE
FROM table_1
FROM table_1 AS alias_1, table_1 AS alias_2
WHERE ...

In this case, there are two instances of table_1 in the second FROM clause. In this case, there is no way of identifying which instance the first FROM clause should be identified with. The usual rules of correlation names apply, and table_1 in the first FROM clause is identified with neither instance in the second clause: there are three instances of table_1 in the statement.

Internally, PowerBuilder processes DELETE, INSERT, and UPDATE statements the same way. PowerBuilder inspects them for any PowerBuilder variable references and replaces all references with a constant that conforms to Adaptive Server Anywhere rules for the data type.

Standards and compatibility 

• SQL/92     Entry level compliant. The use of more than one table in the FROM clause is a vendor extension.

• Sybase     Supported by Adaptive Server Enterprise, including the vendor extension.

Examples 

• Remove employee 105 from the database.

  DELETE
  FROM employee
  WHERE emp_id = 105

• Remove all data prior to 1993 from the fin_data table.

  DELETE
  FROM fin_data
  WHERE year < 1993

• Remove all names from the contact table if they are already present in the customer table.

  DELETE
  FROM contact
  FROM contact, customer
  WHERE contact.last_name = customer.lname
  AND contact.first_name = customer.fname