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 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
from rows_to_adjust
where rows_to_adjust.id = table1.id;
Posted in PostgreSQL

Leave a Reply

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

*