Kickstart 2020 with new opportunities! - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Inserting and updating NULLs
Conditions in UPDATE and DELETE
Updating the list of columns in one query
11. Update a list of columns using a list of values
Using values from another column
Return rows in INSERT, UPDATE, DELETE
Summary

Instruction

Very nice! Now you will learn another trick for updating table records.

If you need to update several columns' values, you don't have to assign values to each column in separate statements. PostgreSQL gives us a short version. Look at the query below:

UPDATE student
SET
  (first_name, middle_name, last_name) =
  ('Tom', 'Edwin', 'Jackson')
WHERE id = 1;

This query changes name data of the student with the id = 1 to Tom Edwin Jackson. Of course, you can write this statement once for each updated column (e.g. SET first_name = 'Tom', middle_name = 'Edwin', ..., etc.). But PostgreSQL allows us to use one SET statement with a list of column names in parentheses (first_name, middle_name, last_name), the =, and a list of new values ('Tom', 'Edwin', 'Jackson').

Exercise

The student with id = 4 is listed as Mary Linda Johnson. That's a mistake; her first name is Marian, she has no middle name, and her last name is Jackson. Correct her data using one SET statement, just as we showed you.