Обсуждение: update via join problem
Running Postgres 7.1.3 on RedHat Linux 7.2.
I have a query where I only want to update one table
and set a column to null.
To figure out what records I need to update, I do
this:
[snip code]
--explain
select count (s.result)
--update
--only sys_results
--set s.result = null
from sys_results s, load_pp_results_ldl_fix_020502 l
where
l.contract::char = s.contract and
l.mbr_num::char = s.mbr_num and
l.type::char = s.type and
l.date = s.date and
s.type='LD' and
s.result='0' and
l.result='NR TRIG HI'
;
[/snip code]
When I change this code to 'update',
I find that the entire table has been updated /
changed to reflect null in the s.result column.
Can someone tell me what I did wrong?
Thanks!
-X
On Mon, 6 May 2002, Johnson, Shaunn wrote: > Running Postgres 7.1.3 on RedHat Linux 7.2. > > I have a query where I only want to update one table > and set a column to null. > > To figure out what records I need to update, I do > this: > > [snip code] > > --explain > select count (s.result) > --update > --only sys_results > --set s.result = null > from sys_results s, load_pp_results_ldl_fix_020502 l > where > l.contract::char = s.contract and > l.mbr_num::char = s.mbr_num and > l.type::char = s.type and > l.date = s.date and > s.type='LD' and > s.result='0' and > l.result='NR TRIG HI' > ; > [/snip code] > > When I change this code to 'update', > I find that the entire table has been updated / > changed to reflect null in the s.result column. > > Can someone tell me what I did wrong? You probably don't want to join with a copy of sys_results in the from of the update (the table specified in update is already joined). You'll need to change the s. to sys_results. in the where clause as well.