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.name FROM journey_patterns jp JOIN routes r ON jp.route_id = r.id JOIN lines l ON r.line_id = l.id WHERE l.name IS NOT NULL AND 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_name FROM journey_patterns jp JOIN routes r ON jp.route_id = r.id JOIN lines l ON r.line_id = l.id WHERE l.name IS NOT NULL AND l.name <> '' ) UPDATE journey_patterns jp SET name = ljp.line_name || ' - ' || jp.name FROM line_journey_pattern ljp WHERE 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.