SQL, how to delete data and tables

To remove data from a SQL table you use the DELETE FROM command. To remove the table itself, structure included, you use DROP TABLE. Let’s see both, because they do very different things.

Suppose we have a customers table with three rows:

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  city VARCHAR(50)
);

INSERT INTO customers VALUES (1, 'Flavio', 'Milan');
INSERT INTO customers VALUES (2, 'Sara', 'Rome');
INSERT INTO customers VALUES (3, 'Luca', 'Milan');

How to delete specific rows

You almost always want a WHERE clause. It tells the database which rows to remove:

DELETE FROM customers WHERE id = 2;

This removes only Sara’s row. The other two rows stay untouched.

The condition can match more than one row. This removes every customer in Milan:

DELETE FROM customers WHERE city = 'Milan';

The database tells you how many rows it deleted. In PostgreSQL you get DELETE 2 back. That count is useful feedback: if you expected to delete one row and you see DELETE 50, something is wrong with your condition.

What happens without WHERE?

If you omit the WHERE clause, DELETE removes every row in the table:

DELETE FROM customers;

The table still exists after this. It’s just empty. You can keep inserting new rows into it.

This is the classic pitfall. You mean to delete one row, you forget the WHERE, and the whole table is gone. My advice is to wrap risky deletes in a transaction:

BEGIN;
DELETE FROM customers WHERE city = 'Milan';
-- check the result, then:
COMMIT;

If the delete touched more rows than you expected, run ROLLBACK instead of COMMIT and nothing is lost.

Another trick: before running a DELETE, run a SELECT with the same WHERE clause. You’ll see exactly which rows are about to disappear.

How to delete the table itself

DROP TABLE removes the table completely: the data, the columns, the indexes, everything.

DROP TABLE customers;

After this, any query on customers fails, because the table no longer exists.

If you try to drop a table that doesn’t exist, you get an error. You can avoid that with IF EXISTS:

DROP TABLE IF EXISTS customers;

This is handy in setup scripts you run multiple times.

One last note: DELETE can be rolled back inside a transaction, and it fires any ON DELETE triggers or foreign key rules. DROP TABLE is a structural change. Use it only when you truly want the table gone.

Related posts about database: