How to run update query on multiple table via joins simultaneously on postgres?

Question

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.

Answer 1

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.

Answer 2

It is not possible in postgresql as the writing scope for set clause is restricted to the table mentioned in update part, which permits only one table, although other tables can be specified in from clause and then they can be utilized in the reading scope of set clause.