Posted by Bart op de grote markt on 08/31/07 22:01

On 31 aug, 23:33, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Bart op de grote markt (warn...@googlemail.com) writes:
>
> > I have a problem with a subselect I use in a stored procedure:
>
> > UPDATE #TEMP_TABLE
> > SET P_ID_1=(SELECT top 1 b.P_ID_1 from #TEMP_TABLE b where
> > b.ID=PARENT_ID),
> > P_ID_2=PARENT_ID,
> > P_ID_3=ID
> > WHERE PARENT_ID IN (SELECT P_ID_2
> > FROM #TEMP_TABLE b)
>
> > So the subselect is (SELECT top 1 b.P_ID_1 from #TEMP_TABLE b where
> > b.ID=PARENT_ID), and it returns NULL. The cause of that is most
> > probably the fact that I try to link ID from inner table b with
> > PARENT_ID from the outer table. I thought it had to be done this way,
> > but obviously not. Can somebody help me with this syntax problem?
>
> Since there is no prefix to PARENT_ID in the subselect, the column is
> taken from the innermost table. That is the subselect is really:
>
> (SELECT top 1 b.P_ID_1 from #TEMP_TABLE b where
> b.ID=b.PARENT_ID)
>
> It's good practice to prefix all columns in a query. (Save for queries that
> involve one single table.) That avoids nasty surprised like this one.
>
> So write:
>
> UPDATE #TEMP_TABLE
> SET P_ID_1 = (SELECT top 1 b.P_ID_1
> from #TEMP_TABLE b
> where b.ID = a.PARENT_ID),
> P_ID_2=a.PARENT_ID,
> P_ID_3=a.ID
> FROM #TEMP_TABLE a
> WHERE a.PARENT_ID IN (SELECT c.P_ID_2
> FROM #TEMP_TABLE c)
>
> But what is the subquery intended to achieve. You have a TOP, but there
> is no ORDER BY clause. Does this mean that if there are several child
> rows, any will do?
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Ah ok, yeah sorry I had to delete that "top 1", it was just to test if
the problem was not that the subquery returned more than one row,
which seems unlogical, but you never know... So that was my
mistake...
But well I learned from your post that you put a FROM-clause in your
update. I did not know that you could do that. Unfortunately I can
only test it on monday again... Thx for your input!

[Back to original message]