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

By

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 its order_id and its customer_id.
  • order_details: one row per order line, with the order_id it belongs to, a product_id and a quantity. Its order_id is 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 from order_details.
  • Then delete order 10251 itself from orders.
  • Keep orders 10248, 10249 and 10250, 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.

Cheat sheets