Re: BUG #5972: Update with subquery: erroneous results for foreign key field

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
>