Adam Gordon Bell - Postgres Update With Join

Postgres Update With Join

2016-04-20 - postgres, cte, update

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;

Hey, this is my blog

Postgres Update With Join

Writing and testing the select as a cte

Testing the select as an update, without applying changes

Run it

About

Elsewhere