Update a PostgreSQL table using a WITH query
I often need to update a table using values from a different table and most of the time the quickest and also dirtiest solution is simply to do a subquery. A better and cleaner solution is to use the UPDATE .. SET .. FROM:
Let's describe our case with an example:
I have a table
journey_pattern in which I need to update the name based on the name of its line. I can find the line by joining 2 other tables:
Using a subquery I can simply do:
UPDATE journey_patterns p SET name = (SELECT l.nameFROM journey_patterns jpJOIN routes r ON jp.route_id = r.idJOIN lines l ON r.line_id = l.idWHERE l.name IS NOT NULLAND l.name <> ''AND jp.id = p.id) || ' - ' || name
But what if
lines.name is empty or null ? I need to check that case using a
CASE .. THEN .. ELSE .. END. The inline subquery will end up being quite difficult to read.
As you can see on the Postgresql UPDATE documentation, we can use a WITH QUERY to update our table:
So in our case:
WITH line_journey_pattern AS (SELECT jp.id AS journey_pattern_id, l.name AS line_nameFROM journey_patterns jpJOIN routes r ON jp.route_id = r.idJOIN lines l ON r.line_id = l.idWHERE l.name IS NOT NULLAND l.name <> '')UPDATE journey_patterns jpSET name = ljp.line_name || ' - ' || jp.nameFROM line_journey_pattern ljpWHERE ljp.journey_pattern_id = jp.id;
WITH statement is used to regroup a
journey_pattern with a
lines.name and we only return line names being not null and not empty.
We can then do a
UPDATE .. SET .. FROM .. WHERE to update our
And the cherry is that this update is done only when
lines.name is set.