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: routes and lines.
Using a subquery I can simply do:
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:
The 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 journey_pattern.name.
And the cherry is that this update is done only when lines.name is set.
You can also follow me on Twitter if you’d like, I share mostly geeky coding stuff: https://twitter.com/m4nu56 and visit my blog: https://m4nu56.dev
