Level 4 · Remove bad lines with DELETE

DELETE removes whole rows. Like UPDATE, it changes every row its WHERE matches, and without a WHERE it matches every row.

DELETE FROM … WHERE

DELETE FROM lines_2025 WHERE quantity <= 0;
DELETE FROM lines_2025 WHERE status IN ('cancelled', 'pending');

You can write it as one statement with OR, but two statements read like a checklist and each can be previewed on its own. Preview first:

SELECT status, COUNT(*) AS lines
FROM lines_2025
WHERE quantity <= 0 OR status IN ('cancelled', 'pending')
GROUP BY status;

On the 2025 copy, the staging table starts with 12,017 lines: 10,810 completed, 296 refunded, 684 cancelled and 227 pending. Ten completed lines have a quantity of zero or below, the data-entry errors documented in the dataset. After both deletes, 10,800 completed lines (21,365 units) and 296 refunded lines (597 units) remain.

Refunded orders stay on purpose: they did ship and came back, so the warehouse handled them. Deciding what to keep is a business question; ask the person who requested the table rather than guessing.

DELETE vs TRUNCATE vs DROP

Three statements remove data, at three different levels:

statement	removes	table afterwards
DELETE FROM t WHERE ...	the matching rows	still there, other rows kept
TRUNCATE t (or TRUNCATE TABLE t)	every row, fast	still there, empty, same columns
DROP TABLE t	the table itself	gone, columns and all

Use DELETE to clean selected rows, TRUNCATE to empty a load table before tomorrow’s import, and DROP TABLE when the table is no longer needed at all, such as a scratch table at the end of an analysis. DROP TABLE IF EXISTS t avoids an error when it is already gone.

Should you delete at all?

On the store’s real order_items, deleting the bad lines would erase evidence: support could no longer see that an order had a zero-quantity line. In source tables, analysts filter (WHERE quantity > 0) or flag (CASE WHEN quantity <= 0 THEN 'bad_qty' END) instead. Deleting belongs in staging copies like this one, where the source is untouched.

Common mistakes

• Running a DELETE with no WHERE. Some editors run the selected text only, so a half-selected statement can be the whole table.
• Using < instead of <= and keeping the eight zero-quantity lines in the full dataset.
• Using NOT IN ('completed', 'refunded') when a status column has NULLs: rows with a NULL status would survive, because NULL NOT IN (...) is unknown.
• Dropping a table when you only wanted it empty.

In SQL Server

DELETE and DROP TABLE IF EXISTS (2016+) are the same. Emptying a table is TRUNCATE TABLE dbo.Lines2025; (the word TABLE is required). In SSMS, a habit worth keeping:

BEGIN TRAN;
DELETE FROM dbo.Lines2025 WHERE Quantity <= 0;
SELECT @@ROWCOUNT AS deleted_rows;  -- check, then COMMIT or ROLLBACK