-2

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?

Tech buddy
  • 1
  • 2
    The correct syntax is documented [in the manual](https://www.postgresql.org/docs/current/sql-update.html) –  Nov 30 '21 at 14:05
  • 1
    ___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
  • 1
    Can you please provide me with a query based on above scenario? – Tech buddy Nov 30 '21 at 14:12
  • ERROR: syntax error at or near "join" – Tech buddy Nov 30 '21 at 14:14
  • 1
    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
  • 1
    PostGreSQL does not support JOINs in an UPDATE like Microsoft SQL do. – SQLpro Nov 30 '21 at 14:26
  • 1
    I want to set a static value that's why I haven't use FROM – Tech buddy Nov 30 '21 at 14:37
  • 1
    @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

2 Answers2

1

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.

Adrian Klaver
  • 15,886
  • 2
  • 17
  • 28
1

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/

Saee Saadat
  • 629
  • 5
  • 9