Nifty PostgreSQL Common Table Expression Update

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, coalesce(, as name_to_set from table1
left outer join table2 on table1.table2_id =
left outer join table3 on table1.table3_id =
where is null
update table1 set name = rows_to_adjust.name_to_set
from rows_to_adjust
where =;
Posted in PostgreSQL

Leave a Reply

Your email address will not be published. Required fields are marked *