SQL and MySQL course Β· Module 5: Modifying data (DML)

DELETE - taking a scroll off the shelf

2 min read Β·
In this lesson3

When a book is destroyed or a reader withdraws from the Library, the scribe removes the matching row with the DELETE FROM command.

Syntax

DELETE FROM czytelnicy
WHERE id = 12;

Two parts:

  • DELETE FROM czytelnicy - which shelf we are removing from.
  • WHERE id = 12 - which row is to disappear.

Notice that with DELETE we do not provide any columns - we remove the whole row, not a single field. (To clear just one field, we use UPDATE ... SET field = NULL.)

Removing many rows

WHERE may point to more than one row:

DELETE FROM ksiazki
WHERE dostepna = 0;

This will remove all books marked as unavailable.

SECOND GREAT WARNING: always WHERE!

This command looks harmless, yet it is the most dangerous of them all:

DELETE FROM czytelnicy;

Without WHERE it removes ALL readers - the whole shelf is emptied! There is no trash bin, no undo. The scrolls are gone.

That is why an experienced scribe has an iron habit: write WHERE first, only then the rest of the command. Some even check the condition with SELECT * FROM table WHERE ... ended with a ";" to see which rows will disappear before they issue the DELETE.

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. 1. What will the command do: DELETE FROM czytelnicy; (without a WHERE clause)?

  2. 2. Why do we not provide column names with DELETE?

Hands-on tasks in the game

  • Code editor

    Write a command that removes from the czytelnicy table only one reader - the one with id = 12. Be sure to use the WHERE clause.

  • Horizontal ordering

    Arrange the query elements on one line in the correct order (left to right):

  • Horizontal ordering

    Arrange on one line the command that removes one row (left to right):

  • Code editor

    Write a command that removes from the wypozyczenia table only the entry with id = 3. Be sure to use the WHERE clause.

  1. QuestionDangerous DELETE without WHERE
  2. QuestionNo columns after DELETE
  3. Coding taskDelete a reader
  4. Put in orderA DELETE command word by word
  5. Put in orderDeleting the reader with id 12
  6. Coding taskDelete a loan

Useful articles