Removing rows with DELETE

DELETE removes the rows that match a WHERE condition:

DELETE FROM product WHERE code = 'P03';

Only the matching rows are removed; the table and its other rows stay.

DELETE with a WHERE removes only the matching row

The same warning

Like UPDATE, a DELETE with no WHERE hits every row:

DELETE FROM product;   -- empties the whole table!

Always check your WHERE before running a DELETE. The checker runs your DELETE, then reads the table back to confirm the right row is gone.

Common mistakes

DELETE without a WHERE empties the table — always add the WHERE.
Test a risky delete first as a SELECT with the same WHERE.

Delete the product with code 'P03' from the product table. (Afterwards only P01 and P02 remain.)

DELETE FROM product WHERE ...;

Click Run to see the output here.

Delete can use any condition. Remove every product that costs less than 2.00 with WHERE price < 2.00. Only the Pen (0.80) goes; check which rows remain.

DELETE FROM product WHERE price < ...;

Click Run to see the output here.

Deleting rows

Removing rows with DELETE

The same warning

Common mistakes

Handout

Log in or create account

Feedback & help