D’ja ever want to target a specific group of rows that meet a condition in a database table, join it with information from other tables, and then perform an update on them in a single command? PostgreSQL’s Common Table Expressions (CTE) work pretty handily for doing just that, though I rarely see the solution posted conspicuously.
In the case below, I am trying to set a new name field in table 1 based on the names of some underlying tables. The record that has the name might be in table 2 or table 3.
with rows_to_adjust as (
select table1.id, coalesce(table2.name, table3.name) as name_to_set from table1
left outer join table2 on table1.table2_id = table2.id
left outer join table3 on table1.table3_id = table3.id
where table1.name is null
update table1 set name = rows_to_adjust.name_to_set
where rows_to_adjust.id = table1.id;