From: | Paul Cocei <paul(dot)cocei(at)punct(dot)ro> |
---|---|
To: | Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #5972: Update with subquery: erroneous results for foreign key field |
Date: | 2011-04-12 14:13:13 |
Message-ID: | BANLkTi=RjiyJuU70s=tcQkFXopC=FOvvNQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Thank you very much, i understand now :)
Paul
On Tue, Apr 12, 2011 at 4:42 PM, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov
> wrote:
> "Paul" <paul(dot)cocei(at)punct(dot)ro> wrote:
>
> > update cart set status = 1 where userid = (select userid from
> > users where email = 'example(at)example(dot)com');
> >
> > As you can see, the subquery is broken (users table doesn't have
> > the column userid).
>
> By standard, if the identifier isn't defined within the most local
> scope, each enclosing scope, from the inside out, will be checked.
> I would expect the above to update each row where cart.userid was
> not null.
>
> I always use and recommend aliases where practical. If you wrote it
> this way, such a mistake would be clearly identified:
>
> update cart set status = 1 where userid = (select u.userid from
> users u where u.email = 'example(at)example(dot)com');
>
> Not a bug.
>
> -Kevin
>
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2011-04-12 14:52:55 | Re: Missing documentation for error code: 80S01 |
Previous Message | Merlin Moncure | 2011-04-12 13:57:45 | Re: PostgreSQL backend process high memory usage issue |