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

Question

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?

The correct syntax is documented [in the manual](https://www.postgresql.org/docs/current/sql-update.html) — a_horse_with_no_name, Nov 30 '21 at 14:05
___but it is giving me error on join.___ Well dont you think it would be helpful if you told us WHAT ERROR — RiggsFolly, Nov 30 '21 at 14:08
In this case I want to join and update Table 1 with Table 2, Table 2 with Table 3, Based on table1.id — Tech buddy, Nov 30 '21 at 14:09
Can you please provide me with a query based on above scenario? — Tech buddy, Nov 30 '21 at 14:12
There are many problems that you may encounter when updating multiple tables. Perhaps the resources mentioned here will help you: https://stackoverflow.com/questions/32386246/update-statement-with-multiple-joins-in-postgresql — David Lukas, Nov 30 '21 at 14:16
I noticed that you have the `set` beyond missing `from`. `update ... set ... from ... where ...` — David Lukas, Nov 30 '21 at 14:21
PostGreSQL does not support JOINs in an UPDATE like Microsoft SQL do. — SQLpro, Nov 30 '21 at 14:26
@Techbuddy It's probably an update of multiple tables in a single query. Then the following might help: https://stackoverflow.com/questions/29898244/postgresql-update-multiple-tables-in-single-query — David Lukas, Nov 30 '21 at 14:58

score 1 · Answer 1 · answered Nov 30 '21 at 16:57

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.

score 1 · Answer 2 · answered Nov 30 '21 at 17:31

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/

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

2 Answers2