# 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 November 16, 2026

By Gregor Dzierzon

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:

```sql
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

```sql
DELETE FROM products
WHERE category_id = 9;
```

This query deletes all products with a `category_id` of 9.

### Deleting Rows Based on Multiple Conditions

```sql
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.

```sql
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.

```sql
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.

```sql
SELECT *
FROM products
WHERE product_id = 15;
```

Then execute the `DELETE` after verifying that the correct product was targeted with the `WHERE`.

```sql
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

- [ANSI Standard Syntax for DELETE](https://www.ask-codey.com/cheat-sheets/deleting-data-ansi-standard-syntax-for-delete-cheat-sheet)

Source: https://www.ask-codey.com/lessons/deleting-data-ansi-standard-syntax-for-delete
