problem with subselect

Hello,

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?

Thx,

Bart

Re: problem with subselect

Please post table structure. Also let us know what you want to update.

-
Shiju Samuel

On Aug 31, 6:58 pm, Bart op de grote markt
wrote:
> Hello,
>
> 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?
>
> Thx,
>
> Bart

Re: problem with subselect

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/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: problem with subselect

On 31 aug, 18:20, Shiju Samuel wrote:
> Please post table structure. Also let us know what you want to update.
>
> -
> Shiju Samuel

Since I am not at work now, I can't copy the whole stored procedure
that I had till now; but concerting the table structure, it is like
this
#TEMP_TABLE with columns ID, PARENT_ID, SEQUENCE, DESCRIPTION, P_ID_1,
P,_ID_2, P_ID_3,...,P_ID_10
The records in the #TEMP_TABLE are first imported from a databasetable
(only columns ID, PARENT_ID, SEQUENCE, DESCRIPTION) and they represent
a tree-like structure with the root-nodes having a PARENT_ID-value of
NULL. Now I want the stored procedure to return every node with all
of its parents until the root node.(which will be in P_ID_1) E.g. 1 >
1A > 1A1 will be the result for record [1A1 | 1A | 3 | description of
1A1]

So the first step is importing the data into #TEMP_TABLE.
The 2nd step is to put the ID's of all records that have NULL as
PARENT_ID into their P_ID_1 column.
The 3rd step is to put ID's of all records that have a PARENT_ID that
is in (SELECT P_ID_2 FROM #TEMP_TABLE b) , which is a result op step
2, into P_ID_2. We put their parent_ID then into P_ID_1.
The 4th step is the SQL from my first post. It is easy to fill in
P_ID_2 and P_ID_3, but for P_ID_1 I need a subselect, and it doesn't
seem to work like I explained in my first post.


Grtz,

Bart

Re: problem with subselect

On 31 aug, 23:33, Erland Sommarskog 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/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.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!

Re: problem with subselect

Bart op de grote markt (warnezb@googlemail.com) writes:
> 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!

I should point out that this is syntax that is proprietary to SQL Server
and Sybase (and I think Informix has it too). In fact, you can also do:


UPDATE tbl
SET col = ...
FROM tbl
JOIN othertbl ...

just like in a regular SELECT. The one thing to watch out for is that if
your join conditions are such that they include rows from the target
table multiple times, it is unpredictable which value that will win.

While frowned at by purists, it's a very convenient extension, as it
makes your UPDATE (and DELETE) statements easier to write. Also, it is
my experience that an UPDATE with FROM JOIN performs better than using
subqueries in the SET clause. This is particularly evident if you need
to retrieve several columns from the same table. With the ANSI syntax
you need to repeat the subquery each time. (Since SQL Server does not
support set constrctors).



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

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

Re: problem with subselect

Erland Sommarskog wrote in
news:Xns999E7C562A7ACYazorman@127.0.0.1:

> UPDATE tbl
> SET col = ...
> FROM tbl
> JOIN othertbl ...

IMHO it is also worth noting that the tbl immediately following the UPDATE
may be an alias so that one may write

UPDATE a
SET col = ...
FROM tbl a
....

(but the form UPDATE tbl a SET col = ... isn't permitted)

and this can avoid some confusion (to the human reader/writer - of course
the computer executes what was written, even if it isn't what the SQL
writer intended)

Re: problem with subselect

Thx everybody for your help! It works the way I wanted it to and I
learned some new things :).

Regards,

Bart