I have the following simple SQL update:
update t2 set col = 2
from t2, t1
where t2.id = t1.id
and t1.name = 'test';
But all the rows in t2 are updated! i.e. not just the one with the id where name is 'test', how do I achieve this in Postgres?
You've got too much from from t1,t2
do this:
update t2 set col = 2
from t1
where t2.id = t1.id
and t1.name = 'test';
If you must have t2 in the from alias it and join all three tables in the where..
update t2 as t2_up set col = 2
from t2 as t2_from , t1
where t2_from.id = t1.id
and t2_up.id = t2_from.id
and t1.name = 'test';
I'm guessing that you really mean:
update t2
set col = 2
from t2 join
t1
on t1.id = t2.id
where t1.name = 'test';
The condition t1.id = t1.id doesn't really make much sense in a query. It is equivalent to t1.id is not null. Hence, all rows in t2 would get updated due to the cross join.
