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?

Answers


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.

  1. Use the first query that you posted to UPDATE.

  2. 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.


Need Your Help

Loop through an array of objects and add only the value to another array. Knockout

arrays knockout.js

friends. I have the following issue. I have two observable arrays.

dynamically determine the type of integer based on the system (c++)

c++ int long-integer

I am writing a program to store data to a file on the unit of every 32 bits (i.e. 4 bytes at a time). I wrote the code in 64-bit windows system but the compiler I used is 32 bits (mingw32). In the