How to Run Update Query on Multiple Tables via Joins in PostgreSQL

join;postgresqlupdate

In MySQL, I can do a query like this

UPDATE 
    prd_sectionshapename  se
inner join 
    prd_shape s
ON
    s.id = se.shape_id
SET
   se.company_shape_name = 'ABC',
   s.name_en = 'Another name'
WHERE s.serial_number = '1234ST';

In Postgres, I need to split up into 2 queries

UPDATE prd_shape AS s
SET name_en = 'Another name'
WHERE s.serial_number = '1234ST';


UPDATE prd_sectionshapename AS se
SET company_shape_name = 'ABC'
FROM prd_shape s
WHERE se.shape_id = s.id and s.serial_number = '1234ST';

My question is it possible to do it in 1 query just like MySQL?

Note that other postgres update join answers I found on the internet typically show that the update effect happens only on a single table even though a JOIN is used.

My purpose here is to update multiple tables in one query. Not just one table.

Best Answer

I don't see why you would want to do that. Two UPDATE statements in a single transaction will do just fine and won't incur any additional overhead compared to a single statement.

Having said that: it is possible by using a writeable common table expression

with shape_update as (
  UPDATE prd_shape
   SET name_en = 'Another name'
  WHERE serial_number = '1234ST'
  returning id, serial_number
)
UPDATE prd_sectionshapename 
  SET company_shape_name = 'ABC'
WHERE (shape_id, serial_number) IN (select id, serial_number from shape_update);

That solution indeed has one advantage: you have to specify the value for the serial_number only once. But it has no performance or transactional advantages.

Related Question