1

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.

  • What's wrong with having two statements? Simply do it in a single transaction. – a_horse_with_no_name Aug 23 '18 at 5:59
  • shouldn't this question be moved to SO? – UdIt Solanki Aug 23 '18 at 6:59
4

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.