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.

The basic syntax for UPDATE is: UPDATE table SET column1=value1, column2=value2, ... WHERE some_column=some_value;.

One way to format your UPDATE statement to make it easy to read is:

UPDATE student
   SET grade = 'F'
 WHERE exam_points < 25;

The most important things to remember is

  • include a WHERE clause unless you want to update all rows in the table:
    • WHERE isbn='123-123-123-123'
  • use a comma-separated list of column-value pairs if you want to update more than one column:
    • SET email='dumbledore@ait.gu.se', title='Headmaster'

See the videos and presentation (we have video lectures in Swedish and English), read the pages linked to below (under External links) and move on to the exercises in the next chapter.

Referencing column values

One useful feature of the UPDATE statement, is that you can use existing values from column as part of the value for a column. Let's show you an example which explains what we mean. Let's say we have a table with employee data:

  • first_name
  • last_name
  • email

The table has data for first_name and last_name but no employee has an email address yet. The company, The Great Northern Hotel, has recently set up email addresses for the employees and decide that all employees should have an email address on the form firstname.lastname@greatnorthern.com . All rows lack data for the email column but has data for the first names and last names. We can now update the email column, referring the first_name and last_name columns.

First, let's see how we can convert a text string to all lower case:

sqlite> SELECT LOWER('CAN YOU GET ANY LOWER?');
can you get any lower?

The LOWER function takes care of that. Next, let's see how we can concatenate strings:

sqlite> SELECT 'inf' || 'ologisk';
infologisk
sqlite> SELECT 'a' || 'pes' || 'hit';
apeshit

The || operator concatenates strings (glues them together).

So, what we want to do in order to produce email addresses for all employees, is to concatenate the four strings first_name . last_name @greatnorthern.com . Let's look at the table, the data before, the UPDATE statement and the result:

sqlite> .schema employees
CREATE TABLE employees(emp_id INTEGER PRIMARY KEY NOT NULL, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT);
sqlite> SELECT * FROM employees;
1|Dale|Cooper|NULL
2|Laura|Palmer|NULL
3|Harry|Truman|NULL
4|Audrey|Horne|NULL
sqlite> UPDATE employees SET email = lower(first_name || '.' || last_name || '@greatnorthern.com');
sqlite> SELECT * FROM employees;
1|Dale|Cooper|dale.cooper@greatnorthern.com
2|Laura|Palmer|laura.palmer@greatnorthern.com
3|Harry|Truman|harry.truman@greatnorthern.com
4|Audrey|Horne|audrey.horne@greatnorthern.com
sqlite>

You can get the SQL for the table without values for email, if you want to try it out. It's here: employees.sql. You can create the database emp.db like this:

$ sqlite3 emp.db < employees.sql

Note that the absence of a value for a column in a row, is the special NULL value. We'll talk more about NULL in a later lecture. If you want to see the word NULL in all columns with a null value, like in the example above, you can use:

sqlite> .nullvalue NULL

Links

Swedish videos

English videos

Source code

Further reading

Where to go next

Next page contains the exercises on UPDATE.

« PreviousBook TOCNext »