I am trying to run a postgresql query in which I need to join three tables as part of update query b-CodePudding

I am trying to run a postgresql query in which I need to join three tables as part of update query but it is giving me error on join.

UPDATE table1 
join table2 on (table1.id=table2.id1)
join table3 on (table2.id2=table3.id)
SET table1.name='Test',
table3.equipment_code='ABC'
WHERE table1.id='21003';

Can you please guide me accordingly?

CodePudding user response：

Not tested but something like this:

UPDATE table1 
   SET table1.name='Test'
FROM
   table1 join table2 on table1.id=table2.id1
   table2 join table3 on table2.id2=table3.id
WHERE
   table3.equipment_code='ABC'
AND 
   table1.id='21003';

Though I am not sure why you are not just doing:

UPDATE table1 
   SET table1.name='Test'
WHERE
   table1.id='21003';

I don't see that table2 and table3 are contributing anything to the UPDATE.

CodePudding user response：

you can only update one table per update query. so what you need is two separate queries like this:

1- update table3 using table1

UPDATE table3 SET equipment_code = t.whatever
FROM (SELECT * FROM table1 JOIN table2 ON table1.id = table2.id1) AS t 
WHERE t.id2 = table3.id AND table1.id = '21003';

2- then update your table1

UPDATE table1 SET name = 'Test' WHERE id = '21003';

btw if you wanna know more about the update-join syntax visit here: https://www.postgresqltutorial.com/postgresql-update-join/