1

I am trying to update a set of rows which needs to be filtered by a JOIN to another table but which will not be doing the actual update.

Example:

The table to be update is called t2 It has a master table called t1. The actual rows to be updated are the rows from t2 where the status on t1 is ACTIVE. The actual field to be updated is called t2.t3_field which will be updated from table t3.

UPDATE t2
JOIN t1
ON t1.id = t2.t1_id
SET t3_field= t3.id
FROM t3
WHERE t3.old_id = t2.old_third_table_id
AND t1.status = 'ACTIVE';

This code does not work. Is there an easy way to achieve this?

Thanks for any help.

Dov

eurosoll
  • 41
  • 6
  • Why doesn't work? Is there any error message? – McNets Dec 19 '18 at 21:18
  • I get an error message: ERROR: syntax error at or near "JOIN" - SQL state: 42601 – eurosoll Dec 19 '18 at 21:21
  • Have a look at the correct syntax on Postgres: https://stackoverflow.com/questions/7869592/how-to-do-an-update-join-in-postgresql – McNets Dec 19 '18 at 21:21
  • 1
    Possible duplicate of [How to do an update + join in PostgreSQL?](https://stackoverflow.com/questions/7869592/how-to-do-an-update-join-in-postgresql) – McNets Dec 19 '18 at 21:21
  • Thanks. But I think that is a different scenario. There the JOIN is on the updating table. In my case the JOIN is on the table which helps to filter the rows to be updated. The table that is updating is a third table. – eurosoll Dec 19 '18 at 21:23
  • Then JOIN the 3rd table, but put the SET statement just after UPDATE. You could use as CTE syntax too. https://stackoverflow.com/a/31718945/3270427 – McNets Dec 19 '18 at 21:26
  • Thanks. I got it working as using a CTE. – eurosoll Dec 19 '18 at 21:46

1 Answers1

4

As documented in the manual, you can't write JOIN before the FROM or the SET clause.

It has to be something like this:

UPDATE t2
  SET t3_field= t3.id
FROM t1, t3
WHERE t1.id = t2.t1_id
  AND t3.old_id = t2.old_third_table_id
  AND t1.status = 'ACTIVE';
  • Thank you. I first tried it using a CTE as above, the the speed of your answer was much faster. Again thanks all for your help. – eurosoll Dec 19 '18 at 21:53