Simple DELETE statements have the following form:
DELETE [ FROM ] table-name WHERE column-name = expression
You can also use a more complex form, as follows
DELETE [ FROM ] table-name FROM table-list WHERE search-condition
Use the WHERE clause to specify which rows to remove. If no WHERE clause appears, the DELETE statement remove all rows in the table.
The FROM clause in the second position of a DELETE statement is a special feature allowing you to select data from a table or tables and delete corresponding data from the first-named table. The rows you select in the FROM clause specify the conditions for the delete. See DELETE statement.
This example uses the SQL Anywhere sample database. To execute the statements in the example, you should set the option wait_for_commit to On. The following statement does this for the current connection only:
SET TEMPORARY OPTION wait_for_commit = 'On'; |
This allows you to delete rows even if they contain primary keys referenced by a foreign key, but does not permit a COMMIT unless the corresponding foreign key is deleted also.
The following view displays products and the value of that product that has been sold:
CREATE VIEW ProductPopularity as SELECT Products.ID, SUM( Products.UnitPrice * SalesOrderItems.Quantity ) AS "Value Sold" FROM Products JOIN SalesOrderItems ON Products.ID = SalesOrderItems.ProductID GROUP BY Products.ID; |
Using this view, you can delete those products which have sold less than $20,000 from the Products table.
DELETE FROM Products FROM Products NATURAL JOIN ProductPopularity WHERE "Value Sold" < 20000; |
Cancel these changes to the database by entering a ROLLBACK statement:
ROLLBACK; |
You can also delete rows from database tables from the Interactive SQL result set. See Editing result sets in Interactive SQL.
Deleting all rows from a table
Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |