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

UPDATE - correcting a scroll

1 min read Β·
In this lesson3

Sometimes data in the Library must change: a book's price rises, a reader changes city, a book returns from a loan. For this we use UPDATE.

Syntax

UPDATE ksiazki
SET cena = 59.90
WHERE id = 5;

The three parts of this command:

  • UPDATE ksiazki - which shelf (table) we are correcting.
  • SET cena = 59.90 - which column and to what value we change it.
  • WHERE id = 5 - which row (or rows) is to be changed.

Changing several columns at once

In a single SET we can change several columns, separating them with commas:

UPDATE czytelnicy
SET miasto = 'Aleksandria', email = 'hypatia@biblioteka.aleksandria'
WHERE id = 7;

MOST IMPORTANT: always WHERE!

Look at this command WITHOUT the WHERE clause:

UPDATE ksiazki
SET cena = 59.90;

This will change the price of ALL books in the Library to 59.90! Every scroll gets the same price, and the original data is lost forever.

WHERE is the scribe's sight. Without it UPDATE strikes the entire shelf. Before you run an UPDATE, always ask yourself: which exact rows do I want to change? - and write the matching WHERE.

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: UPDATE ksiazki SET cena = 59.90; (without a WHERE clause)?

  2. 2. What is the SET clause used for in an UPDATE command?

These are 2 of 3 questions for this lesson. Solve the rest in the game.

Hands-on tasks in the game

  • Code editor

    Write a command that in the ksiazki table sets cena to 59.90, but ONLY for the book with id = 5. Be sure to use the WHERE clause.

  • Horizontal ordering

    Arrange the fragment of the UPDATE command on one line (left to right):

  • Code editor

    Write a command that in the czytelnicy table sets miasto to 'Aleksandria' for the reader with id = 7. Be sure to use the WHERE clause.

  • Click in order

    Click the elements in the order in which we build a command that updates one row:

  1. QuestionDangerous UPDATE without WHERE
  2. QuestionRole of the SET clause
  3. Coding taskRaise a book's price
  4. Put in orderAn UPDATE command on one line
  5. QuestionChanging several columns at once
  6. Coding taskChange a reader's city
  7. Click in orderUpdating the price of one book

Useful articles