Update with where ANSI join syntax in PostgreSQL updates all rows
I'm trying to do an update with joins in the where clause. I understand with PostgreSQL there is a from clause that I can use with implicit joins like this:
update tbl1 t1 set name = 'foo' from tbl2 t2 where t2.id = t1.table2_id and t2.region = 'bar'
However, I have existing code that generates ANSI joins instead of implicit joins. Looking around Stack Overflow, I read that I could do something like this:
update tbl1 set name = 'foo' from tbl1 t1 inner join tbl2 t2 on t2.id = t1.table2_id where t2.region = 'bar'
Unfortunately, this doesn't seem to work, and instead of updating just 2 rows, it updates all the rows, regardless of what's in the from/where clause.
What am I missing?
Yes that is a side effect that is caused due to the reason that it treats t1 as a different table than the one that is being updated. There are 2 ways of getting around this.
Use the first query that you posted to UPDATE.
Add a condition to the second query like tbl1.id = t1.id so that it forces 1 to 1 mapping of the table being updated.