Re: update via join problem

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.