Delete Rows
Sql tutorial · PySpark.in
2.5 DELETING ROWS
What is DELETE?
The DELETE statement is used to remove existing rows (records) from a table.
Using DELETE, we can:
- Delete all rows
- Delete only selected rows
- Remove unwanted data from a table
Real-World Example
Suppose we have a player table.
name | age | score |
|---|---|---|
Virat | 32 | 45 |
Rakesh | 39 | 35 |
Shyam | 41 | 38 |
If a player record is no longer needed, we can remove it using the DELETE statement.
Basic DELETE Syntax
```
hide
DELETE FROM table_name;
```
Understanding the Syntax
Part | Meaning |
|---|---|
DELETE FROM | Removes rows from a table |
table_name | Name of the table |
Delete All Rows
If we do not use a WHERE clause, all rows in the table are deleted.
Example
```
sql
-- Step 1: Create Table
CREATE TABLE player (
name VARCHAR(200),
age INTEGER,
score INTEGER
);
-- Step 2: Insert Data
INSERT INTO player (name, age, score)
VALUES
('Virat', 32, 45),
('Rakesh', 39, 35),
('Sai', 47, 30);
-- Step 3: View Existing Data
SELECT * FROM player;
-- Step 4: Delete All Rows
DELETE FROM player;
-- Step 5: View Table After DELETE
SELECT * FROM player;
```
Important Note
Without a WHERE clause, every row in the table will be deleted.
Delete Specific Rows
To delete only selected rows, we use the WHERE clause.
Syntax
```
hide
DELETE FROM table_name
WHERE condition;
```
Example: Delete Shyam from Player Table
```
hide
DELETE FROM player
WHERE name = 'Shyam';
Select * from player;
```
Flow Diagram
How DELETE with WHERE Works
```
sql
DELETE FROM player
WHERE name = 'Shyam'
```
|
v
Find Row Where name = 'Shyam'
|
v
Delete Matching Row
Common Mistakes in DELETE
Mistake 1: Forgetting WHERE Clause
```
sql
DELETE FROM player;
```
This deletes all rows from the table.
Mistake 2: Wrong Table Name
```
sql
DELETE FROM players_info
WHERE name = 'Shyam';
```
Error
Error: no such table: players_info
Mistake 3: Wrong Column Name
```
sql
DELETE FROM player
WHERE fullname = 'Shyam';
```
Error
Error: no such column: fullname
Complete Example (SQLite/MySQL)
```
sql
CREATE TABLE player (
name VARCHAR(200),
age INTEGER,
score INTEGER
);
INSERT INTO player (name, age, score)
VALUES
('Virat', 32, 45),
('Rakesh', 39, 35),
('Shyam', 41, 38);
SELECT * FROM player;
DELETE FROM player
WHERE name = 'Shyam';
SELECT * FROM player;
```
Final Output
name | age | score |
|---|---|---|
Virat | 32 | 45 |
Rakesh | 39 | 35 |
Summary
Topic | Description |
|---|---|
DELETE | Removes rows from a table |
DELETE FROM | Basic delete command |
WHERE | Deletes only matching rows |
Delete All Rows | Happens without WHERE |
Delete Specific Rows | Uses WHERE clause |
More Sql tutorials
All tutorials · Try the free PySpark compiler · Practice challenges