Updating data

The UPDATE statement writes one or more column values to a row.

Use the UPDATE statement to write one or more column values to a row.

Use the SET clause of the UPDATE statement to assign new column values. UPDATE cannot update the values of primary key columns; instead, delete the row and insert a row with the new primary key column values.

The WHERE clause identifies the row or rows to be updated. To specify a row, the WHERE clause must specify a value for each column of the row's primary key.

To specify more than one row to update, use the IN clause with a list of possible values. You can only do that for the last column of the primary key.

The examples in this section use the rank_by_year_and_name table:

CREATE TABLE cycling.rank_by_year_and_name (
  race_year int,
  race_name text,
  cyclist_name text,
  rank int,
  PRIMARY KEY ((race_year, race_name), rank)
);

This example updates the cyclist name. Notice that a value for each column in the primary key is provided in the WHERE clause.

UPDATE cycling.rank_by_year_and_name
SET cyclist_name = 'John SMITH'
WHERE race_year = 2015
  AND race_name = 'Tour of Japan - Stage 4 - Minami > Shinshu'
  AND rank = 2;

To specify more than one row, use IN with a list of possible values. You can only use IN with a list of values for the last column of the primary key.
```
UPDATE cycling.rank_by_year_and_name
SET cyclist_name = 'Jane DOE'
WHERE race_year = 2015
  AND race_name = 'Tour of Japan - Stage 4 - Minami > Shinshu'
  AND rank IN (2, 3, 4);
```

To perform the update only if the specified row exists, use

IF
                        EXISTS

UPDATE cycling.rank_by_year_and_name
SET cyclist_name = 'John SMITH'
WHERE race_year = 2015
  AND race_name = 'Tour of Japan - Stage 4 - Minami > Shinshu'
  AND rank = 3
IF EXISTS;