Database:Changing rows of data - SQL UPDATE

From Juneday education
Jump to: navigation, search

Meta

Expand using link to the right to see the meta section with instructor's notes.

Description

This chapter deals with the SQL UPDATE statement for changing rows of data in a table.

Prepare

Expect this lecture to take around 10-15 minutes. Again, if you require your students to see the video lecture in advance, you will be able to go faster and can spend more time on questions and answers, interacting with your students.

See the videos and presentation, and review the exercises and suggested solutions online.

Key topics of the lecture:

  • Explain the need for the UPDATE statement by exemplifying how data frequently changes
  • UPDATE meta syntax:
    UPDATE <table> SET <col>=<value>[,<col>=<value>]* WHERE <criteria>;
  • Showing how you can check that your update “worked”
  • Getting the WHERE clause right is very important - what happens otherwise?
  • You can update more than one column at the same time

Topics addressed in the online exercises

The exercises online consists of 9 tasks, and corresponds to 6 A4 pages (when printed). The main topics covered and trained in the exercises are:

  • Changing a single column and multiple columns
  • Making a backup using dump
  • Messing up the database and restoring it from backup
    • In order to create both a good practice of taking backups and a feeling of safety - as long as you can restore things, making a mistake isn’t “dangerous”
    • Stressing the fact that you must be careful when deciding the WHERE clause

Heads-up

Think about:

  • Give a motivation for the existence of the UPDATE statement - why does data change? Does it happen often? Can they think about situations where data changes frequently? Where data almost never changes?
  • If the syntax grammar is confusing to the students, show and refer them to the SQLite online documentation for their graphical syntax diagram
  • Explain and give examples on what happens if:
    • you leave out or make mistakes with the WHERE clause
    • you want to update more than one column at the same time (using commas)
  • It might be good to think about transactions (BEGIN TRANSACTION, COMMIT) if questions arise - but we do not have any chapter dedicated to this in this material - make your own examples and exercises, if you feel it is important

Full frontal - Code examples up-front!

sqlite> UPDATE menu SET type='Vegetarian', healthy='YES' WHERE restaurant='Old Beef House';

Introduction

This chapter deals with the SQL UPDATE statement for changing rows of data in a table.

UPDATE syntax diagram

Many applications allows the user to change data stored in the database. The student administration may, for instance, change the grade of a student using some application. For this, the UPDATE statement is used. It is, of course, important that you learn and master the WHERE clause of an update statement, since that is what you use in order to decide what rows should be updated. It would be problematic if the administration intended to change the grade of one student and all students' grades were changed, for instance.

See the videos and presentation, read the pages linked to below (under External links) and move on to the exercises in the next chapter.

Videos

Note that the videos are in Swedish for now. The presentation, however, is in English

Links

Further reading

Where to go next

Next page contains the exercises on UPDATE.

« PreviousBook TOCNext »