Learning PostgreSQL 11 - Third Edition

UPDATE using sub-select

The expression for a new value is the usual SQL expression. It's possible to refer to the same field in the expression. In that case, the old value is used:

UPDATE t SET f = f + 1 WHERE a = 5;

It's common to use a subquery in the UPDATE statements. To be able to refer to the table being updated from a subquery, the table should get an alias:

car_portal=> UPDATE car_portal_app.a updated SET a_text =   (SELECT b_text FROM car_portal_app.b WHERE b_int = updated.a_int);UPDATE 7

If the subquery returns no result, the field value is set to NULL.

Note that the output of the UPDATE command is the word UPDATE followed by the number of records that were updated.

The WHERE clause is similar to the one used in the SELECT statement. ...