# Deleting Data

Remove rows with DELETE FROM ... WHERE, test the WHERE first, and delete in the order the foreign keys allow.

Apprentice · T-SQL · SQL · Updated November 3, 2026

## After this lesson you can

- Remove rows with DELETE, in an order the foreign keys allow

## Key takeaways

- `DELETE FROM` removes the rows its `WHERE` matches. Without a `WHERE` it tries to remove every row, and there's no undo.
- Test the `WHERE` with a `SELECT` first, then change `SELECT *` into `DELETE` and keep the `WHERE` as it was.
- Delete in the reverse order of inserting: first the rows that point to a row, such as the order lines, then the order itself.
- Business records are often kept, not deleted: Northwind stops selling a product by updating its `Discontinued` column.

`DELETE` removes rows from a table.

```sql
DELETE FROM <table>
WHERE <which rows>;
```

As with `UPDATE`, the `WHERE` decides which rows go, and without it **every row** goes.

## Deleting what you added

Add a shipper, then remove it:

```sql
INSERT INTO Shippers (CompanyName, Phone)
VALUES ('Rapid Delivery', '(503) 555-0110');

DELETE FROM Shippers
WHERE CompanyName = 'Rapid Delivery';
```

```text
(1 row affected)

(1 row affected)
```

## SELECT first, again

The same habit as for `UPDATE`: run a `SELECT` with the same `WHERE`, check the rows, then change `SELECT *` into `DELETE`:

```sql
SELECT *
FROM Shippers
WHERE ShipperId = 4;
```

```sql
DELETE FROM Shippers
WHERE ShipperId = 4;
```

## Foreign keys decide the order

Try to delete product 1, Chai:

```sql
DELETE FROM Products
WHERE ProductId = 1;
```

```text
The DELETE statement conflicted with the REFERENCE constraint "FK_Order_Details_Products". The conflict occurred in database "Northwind", table "dbo.OrderDetails", column 'ProductId'.
```

Chai appears on 38 order lines, and each of them points at product 1 with a foreign key. Deleting the product would leave those order lines pointing at nothing, so the database refuses.

To delete a row that others point to, you first deal with the rows that point to it: delete them, or change them to point somewhere else. The order is the reverse of inserting:

| To insert | To delete |
|---|---|
| the category first, then its products | the products first, then their category |
| the order first, then its order lines | the order lines first, then the order |

For real business data, like Chai and its orders, you usually *don't* delete at all: the sales happened, and the records matter. That's why Northwind's products have a `Discontinued` column: you stop selling a product by updating it, not by deleting it.

## Deleting everything

```sql
DELETE FROM Customers;
```

Without a `WHERE`, this tries to delete every customer. (Here, the foreign keys from `Orders` stop it, but a table nothing points to would simply be emptied.) If you ever really mean to delete every row, write a `WHERE` that says so, such as `WHERE ShipperId > 0`, so it's clearly on purpose.

## Check yourself

2 questions to try on the lesson page.

## So what

A `DELETE` can't be taken back. Test its `WHERE` with a `SELECT`, delete children before parents, and ask whether the row should really be deleted, or only marked as no longer in use.

## Key terms

- [DELETE](https://www.ask-codey.com/glossary/delete)
- [Foreign Key](https://www.ask-codey.com/glossary/foreign-key)

## Cheat sheets

- [Deleting Data](https://www.ask-codey.com/cheat-sheets/modifying-data-in-t-sql-deleting-data-cheat-sheet)

Source: https://www.ask-codey.com/lessons/modifying-data-in-t-sql-deleting-data