The DELETE statement removes any row or combination of rows from a table. You cannot recover a deleted row after the transaction is committed.
Deleting All Rows of a Table
The following query will delete all rows from the customer table.
DELETE FROM customer
Deleting a Known Number of Rows
DELETE FROM customer WHERE customer_num = 175
Deleting an Unknown Number of Rows
DELETE FROM customer WHERE company = 'Druid Cyclery'
Complicated Delete Conditions
Suppose you discover that some rows of the stock table contain incorrect manufacturer codes. Rather than update them, you want to delete them so that they can be reentered. You know that these rows, unlike the correct ones, have no matching rows in the manufact table. The fact that these incorrect rows have no matching rows in the manufact table allows you to write a DELETE statement such as the one in the following example:
DELETE FROM stock WHERE 0 = (SELECT COUNT(*) FROM manufact WHERE manufact.manu_code = stock.manu_code)
INSERT INTO stock VALUES(115, 'PRC', 'tire pump', 108, 'box', '6/box')
Multiple Rows and Expressions
This SELECT statement returns two columns. The data from these columns (in each selected row) is inserted into the named columns of the cust_calls table. Then, an order number (from order_num, a serial column) is inserted into the call description, which is a character column.
INSERT INTO cust_calls (customer_num, call_descr) SELECT customer_num, order_num FROM orders WHERE paid_date IS NOT NULL AND ship_date IS NULL
Selecting Rows to Update
The first form of an UPDATE statement uses a series of assignment clauses to specify new column values, as the following example shows:
UPDATE customer SET fname = 'Barnaby', lname = 'Dorfler' WHERE customer_num = 103
You can also use subqueries in the WHERE clause.
UPDATE orders SET backlog = 'y' WHERE ship_date IS NULL AND order_num IN (SELECT DISTINCT items.order_num FROM items WHERE items.stock_num = 6 AND items.manu_code = 'ANZ')
Updating with Uniform Values
Suppose the manufacturer code HRO has raised all prices by 5 percent, and you must update the stock table to reflect this increase. Use a statement such as the following :
UPDATE stock SET unit_price = unit_price * 1.05 WHERE manu_code = 'HRO'