DELETE

Seznam témat:

popis funkce

příklad

Popis funkce

Use this statement to delete rows from the database.

DELETE  [ FIRST  | TOP  n  ]

... [FROM ] [ owner .]table-name

... [FROM  table-list ]

... [WHERE  search-condition ]

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.

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.

The second FROM clause can contain arbitrary complex table expressions, such as KEY and NATURAL joins. For a full description of the FROM clause and joins, see FROM clause .

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. The statement will fail with a syntax error as it is ambiguous which instance of the table_1  from the second FROM clause matches the first instance of table_1  in the first FROM clause.

Příklad

Vymaže z tabulky employee všechny záznamy, které mají hodnotu sloupce emp_id = 105.

DELETE

FROM employee

WHERE emp_id = 105

Vymaže z tabulky fin_data všechny záznamy, kde sloupec year je menší než 1993

DELETE

FROM fin_data

WHERE year < 1993

Vymaže všechny položky objednávky (tabulka sales_order_items)  včetně hlavičky (tabulka sales_order) kde ship_date < '1994-01-01'  a region ='Olomouc'

DELETE

FROM sales_order_items

FROM sales_order

WHERE sales_order_items.id = sales.order.id and ship_date < '1994-01-01' and region ='Olomouc'

Související témata