problem with subselect

[Bart op de grote markt]

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

[Shiju Samuel]

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 <[e-mail]>
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

[Bart op de grote markt]

On 31 aug, 18:20, Shiju Samuel <[e-mail]:

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

[Erland Sommarskog]

Bart op de grote markt (wa*****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Chris.Cheney]

Erland Sommarskog <es****@sommarskog.sewrote in
news:Xn**********************@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)

[Bart op de grote markt]

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

Regards,

Bart