SQL DELETE
DELETE removes rows from a table.
DELETE Syntax
Example
DELETE FROM table_name
WHERE condition;The WHERE clause chooses which rows are removed. The table itself and its columns stay.
Delete One Row
Sana Iqbal (id 6) has left the institute:
Example
DELETE FROM students
WHERE id = 6;1 row deleted.
Example
SELECT id, name FROM students;| id | name |
|---|---|
| 1 | Ali Khan |
| 2 | Ayesha Siddiqui |
| 3 | Bilal Ahmed |
| 4 | Fatima Noor |
| 5 | Hamza Raza |
Delete Several Rows
Any condition works. This removes all courses that are 3 months long:
Example
DELETE FROM courses
WHERE duration_months = 3;2 rows deleted.
| id | title | duration_months |
|---|---|---|
| 1 | Web Development | 6 |
| 2 | Python Programming | 4 |
Delete All Rows
Without WHERE, every row is deleted:
Example
DELETE FROM students;The table still exists, but it is empty. You can still insert new rows into it.
Deleted rows cannot be brought back unless you have a backup or you are inside a transaction you have not committed yet. Run a SELECT with the same WHERE first and check the rows it shows.
DELETE, TRUNCATE and DROP
These three are easy to mix up:
| Statement | What it removes | Table still exists? |
|---|---|---|
DELETE FROM students WHERE ...; | The matching rows | Yes |
DELETE FROM students; | All rows, one by one | Yes |
TRUNCATE TABLE students; | All rows, very fast | Yes |
DROP TABLE students; | The whole table and its data | No |
TRUNCATE is quicker than DELETE on large tables, but it cannot use a WHERE clause. You will learn DROP TABLE in the tables lesson.
Deleting Rows That Other Tables Use
What if you try to delete the Web Development course while Ali and Bilal are still in it? If the tables are linked with a foreign key, the database stops you with an error, so students do not point to a course that no longer exists:
Example
DELETE FROM courses WHERE id = 1;ERROR: cannot delete or update a parent row: a foreign key constraint fails
The exact wording depends on the database. You must first move or delete the students in that course.
Exercise
Exercise
Which statement removes only the students who live in Lahore?