SQL and MySQL course Β· Module 5: Modifying data (DML)
DELETE versus TRUNCATE - and a summary
In this lesson3
You have already learned the scribe's three great commands. Finally, let us compare DELETE with its faster cousin - TRUNCATE.
DELETE - a precise scalpel
DELETE removes rows selectively, according to WHERE. It can remove one row, several, or (without WHERE) all of them - removing them one by one:
DELETE FROM wypozyczenia WHERE id = 3;TRUNCATE - clearing the whole shelf
TRUNCATE empties the entire table in one move - it is like sweeping all the scrolls off a shelf at once:
TRUNCATE TABLE wypozyczenia;Differences compared to DELETE:
- Clears the whole table - it does not accept
WHERE. - Is faster for large tables - it does not remove row by row.
- Resets AUTO_INCREMENT - the
idcounter returns to 1, as if the shelf were new.
| Trait | DELETE | TRUNCATE |
|---|---|---|
| Row selection (WHERE) | yes | no |
| Speed on large tables | slower | faster |
| Reset AUTO_INCREMENT | no | yes |
A short reminder
DELETE is a scalpel, TRUNCATE is a broom. Both empty shelves, but TRUNCATE does it in one move and starts the id numbering anew. This is a first step - in later locations we will return to this comparison more than once.
Spotted a mistake in this lesson?
Check yourself
Answer the questions from this lesson. Pick an answer to see right away whether it is correct.
1. What does the command TRUNCATE TABLE wypozyczenia; do?
2. How does TRUNCATE differ from DELETE without WHERE?
These are 2 of 3 questions for this lesson. Solve the rest in the game.
Hands-on tasks in the game
- Click in order
Click the elements in the order in which we build a command that clears the whole table:
- Code editor
Write a command that removes from the ksiazki table all books marked as unavailable (dostepna = 0). Use a WHERE clause.