DELETE — Remove Rows

Bumrah is released from the squad and his row must go. DELETE removes rows from a table — just the rows, the table itself stays.

Definition

DELETE is a DML statement that removes existing rows from a table. The WHERE clause decides which rows are removed; without WHERE, all rows are deleted, but the table and its structure remain (as an empty table).

• Removes rows — whole rows, not individual values (to blank one value, UPDATE it to NULL).
• WHERE — the filter; only rows that match are deleted.
• Table remains — unlike DROP, the columns, data types and constraints stay.

Syntax

DELETE FROM table_name
WHERE condition;

NOTE: Always use WHERE to avoid deleting all rows unintentionally.

Notice there is no column list — DELETE name FROM players is wrong. DELETE always removes complete rows.

Example — remove one row

DELETE FROM players
WHERE id = 6;

SELECT * FROM players;

After — Bumrah's row is gone:

Step by step: 1️⃣ WHERE finds id = 6 → 2️⃣ DELETE removes the row. The row is removed from the table for good.

Example — remove several rows with a condition

DELETE FROM players
WHERE country = 'India'
  AND runs < 500;

Dhoni (350) and Bumrah (20) matched both conditions → (2 rows affected). Rohit has exactly 500, which is not < 500, so he stays.

DANGER — DELETE without WHERE

DELETE FROM players;

After — ALL rows deleted!

1️⃣ No WHERE = no filter → 2️⃣ EVERY row is deleted! The table still exists, but it is now completely EMPTY.

Deleting rows that other tables reference

If another table has a FOREIGN KEY pointing at a row (e.g. a contracts row pointing at team 'GT'), SQL Server blocks the delete of that parent row: "The DELETE statement conflicted with the REFERENCE constraint…". Delete the child rows first, or define the foreign key with ON DELETE CASCADE.

Common mistakes

• ❌ DELETE * FROM players WHERE id = 6 (MS Access style). ✅ DELETE FROM players WHERE id = 6 — no *.
• ❌ DELETE name FROM players to blank a column. ✅ UPDATE players SET name = NULL … (or ALTER TABLE … DROP COLUMN to remove the column).
• ❌ Forgetting WHERE → the table is emptied. ✅ Run the same WHERE with SELECT first.
• ❌ WHERE team = NULL → 0 rows deleted. ✅ WHERE team IS NULL.
• ❌ Using DROP TABLE to remove rows. ✅ DROP destroys the table; DELETE removes rows.

SQL Server vs MySQL / PostgreSQL

DELETE FROM table WHERE … is identical everywhere. In SQL Server the word FROM is actually optional (DELETE players WHERE id = 6 works) — but always write it for clarity and portability.

Interview tip

Q: After DELETE FROM players (no WHERE), does the players table still exist? A: Yes. DELETE removes rows only, so the table, its columns and constraints remain — it just has 0 rows. To remove the table itself you need DROP TABLE.