ANSI Standard Syntax for DELETE
Remove rows with DELETE FROM, always limited by a WHERE clause you've checked with a SELECT first.
Apprentice · SQL · MySQL · Updated
The ANSI standard syntax for a DELETE statement in SQL is used to remove rows from a table. Here’s the basic syntax and a few examples to illustrate its use:
DELETE FROM table_name
WHERE condition;
table_name: The name of the table from which you want to delete rows.
WHERE condition: The condition that specifies which rows should be deleted. If omitted, all rows in the table will be deleted.
Basic Examples of Using the DELETE Statement
Deleting Specific Rows Based on a Condition
DELETE FROM products
WHERE category_id = 9;
This query deletes all products with a category_id of 9.
Deleting Rows Based on Multiple Conditions
DELETE FROM orders
WHERE order_date < '2024-01-01'
AND shipped_date IS NULL;
This query deletes every order placed before January 1, 2024, that has not been shipped.
A foreign key protects deletes too. If an order still has rows in order_details, MySQL refuses to delete it (error 1451: Cannot delete or update a parent row). Delete the child rows first, the order's order_details, and then the order. The same goes for a product that appears in order_details and for a customer who still has orders.
Deleting All Rows in a Table
Be very cautious with this, as it will remove all rows from the table. Like the UPDATE statement, MySQL Workbench will not allow the following DELETE statement by default.
DELETE FROM customers;
If you actually want to delete ALL rows from a table, a better approach might be to include a WHERE clause that includes all rows. This makes the DELETE intentional, and prevents accidental deletion of all rows.
DELETE FROM products
WHERE product_id > 0;
This query deletes all rows from the products table, effectively emptying the table.
Summary
Use the WHERE Clause
Always use the WHERE clause to specify which rows to delete. Omitting it will result in all rows being deleted.
SELECT *
FROM products
WHERE product_id = 15;
Then execute the DELETE after verifying that the correct product was targeted with the WHERE.
DELETE FROM products
WHERE product_id = 15;
Back-Up Your Data
Before performing delete operations, especially on large or important tables, it’s a good practice to back up the data.
Verify Conditions
Double-check your conditions to ensure you are deleting the correct rows.
By following the ANSI standard syntax and these best practices, you can safely and effectively use the DELETE statement to remove data from your SQL tables.
Do it: remove an order typed in twice
A new clerk typed in Hanari Carnes's order twice. Order 10250 is the real one; order 10251 is the copy, with the same two order lines. Remove the copy and its lines, and leave every other order as it is.
There are two tables (the Tables tab shows the SQL that makes them):
orders: one row per order, with itsorder_idand itscustomer_id.order_details: one row per order line, with theorder_idit belongs to, aproduct_idand aquantity. Itsorder_idis a foreign key: each line must point to an order that exists.
Here is every order and its lines:
| order_id | customer_id | Its lines (product_id × quantity) |
|---|---|---|
| 10248 | VINET | 11 × 12, 42 × 10 |
| 10249 | TOMSP | 14 × 9 |
| 10250 | HANAR | 41 × 10, 51 × 35 |
| 10251 | HANAR | 41 × 10, 51 × 35 (the copy) |
After your SQL runs, the check lists each order that's left, with how many lines it has, sorted by order_id. It should give exactly these rows:
| order_id | lines |
|---|---|
| 10248 | 2 |
| 10249 | 1 |
| 10250 | 2 |
Your job: write the DELETE statements that remove order 10251.
- First, delete order
10251's two lines fromorder_details. - Then delete order
10251itself fromorders. - Keep orders
10248,10249and10250, and all their lines.
The foreign key decides that order: if you delete the order while its lines still point to it, the database refuses. MySQL says Cannot delete or update a parent row; this box says FOREIGN KEY constraint failed.
Practiced live on the lesson page: write the SQL right there and check it.