UPDATE

The syntax of the UPDATE statement:

UPDATE <table name>
SET <column name> = [<new value>, CASE ... END]
WHERE <search column name> = [<search value>, IN]

The UPDATE ... SET ... WHERE statements works as follows:

1. Table name is specified – UPDATE <table name>, where the data will be updated;
2. Column name is indicated – SET <column name>, where the data will be updated;
3. New value is set – <new value>;
4. Search criteria are specified – WHERE with the indication of the search column <search column name> and the value that the search criterion should match <search value>. If CASE is used, then the IN operator is specified with a list of values <column name>.

Updating a single row in a table with conditions

UPDATE people
SET name = 'Alexander'
WHERE lastname = 'Doe';

UPDATE people
SET age = 31
WHERE country = 'USA' AND city = 'Los Angeles';

In the example "Update with conditions", the condition combining operator AND is used – the condition will only be satisfied when both parts meet the truth conditions. The operator OR can also be used – the condition will be satisfied if at least one part meets the truth conditions. There can be multiple condition operators:

UPDATE people
SET age = 31
WHERE country = 'USA' AND city = 'Los Angeles' OR city = 'Florida';

Updating a single record in a table using expressions or functions:

Frequently during updates, it is necessary to perform mathematical actions on the data or to modify it using functions.

UPDATE people
SET age = age + 1
WHERE country = 'Canada';

UPDATE people
SET name = UPPER(name)
WHERE country = 'USA';

Updating multiple fields of a table row

Data can be updated in multiple columns simultaneously. For this, a list of <column name> = <column new value> is made after the keyword SET:

UPDATE people
SET country = 'Russia', city = 'Moscow'
WHERE lastname = 'Smith';

Updating multiple rows in a table using the CASE ... ENDconstruction

For simultaneous updating of different values in different rows, the CASE ... END instruction can be used with nested data selection conditions WHEN <column name> <condition> (=, >, <) THEN <new value>. This is followed by the WHERE <column name> IN (<column value>, ...) construct, which allows setting a list of values for which the condition will be executed.

Example of changing the age (age) of people (people) depending on their names:

UPDATE people
SET age = CASE
            WHEN name = 'John' THEN 32
            WHEN name = 'Jane' THEN 26
          END
WHERE name IN ('John', 'Jane');