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