Re: update via join problem

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: "Johnson, Shaunn" <SJohnson6(at)bcbsm(dot)com>
Cc: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: update via join problem
Date: 2002-05-06 21:54:29
Message-ID: 20020506145300.P51931-100000@megazone23.bigpanda.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

In response to

Browse pgsql-general by date

  From Date Subject
Next Message wsheldah 2002-05-06 22:11:05 Re: help with *mysql*
Previous Message pilsl 2002-05-06 21:45:15 help with *mysql*