Posted by Erland Sommarskog on 08/31/07 21:33

Bart op de grote markt (warnezb@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]