Hey, this is my blog

It is somewhat abandoned.

Postgres Update With Join

How to perform and update with a join using CTE’s in postgres

  • in a cte, write a select that returns the update to values, and the join on conditions
  • join on that in the update
  • Test: Optionally write a select that tests your results and wrap it all in a rolled back transaction to test run

Writing and testing the select as a cte

with replacement as (
 select value, condtion1, condtion2 from somewhere
  where x= something
 )
 select * from replacement

The cte might seem verbose, but I’m going to need it later, and it helps my sql stay organized

Testing the select as an update, without applying changes

BEGIN;


with replacement as (
 select value, condtion1, condtion2 from somewhere
  where x= something
 )

 update "LongName" as shortname
 set value = replacement.value
 from replacement
 where shortname.condtion1 = replacement.condtion1
 and shortname.condtion2 = replacement.condition2;

-- Test query for manually confirming the results look like what you were expecting
SELECT value, condition1, condition2 from "LongName" shortname
where x=something

 ROLLBACK;

If the select result looks good, you can run it without the transaction to update.

Run it

with replacement as (
 select value, condtion1, condtion2 from somewhere
  where x= something
 )

 update "LongName" as shortname
 set value = replacement.value
 from replacement
 where shortname.condtion1 = replacement.condtion1
 and shortname.condtion2 = replacement.condition2;