Using the result of aSELECT as criteria for another SELECT

[simco]

Hi all,

I want to do something like:

SELECT ID_Field from STable where [here comes a long list of conditions that I need to build dynamicaly]

Then I want to be able to say:

Update DTable set Field='Value' where ID_Field in (here I want to put the result from the above select).

(all the above code goes into a trigger)

I was trying to create a cursor from the first SELECT, but, reading the post I understand that this is not a recommended solution.
So, what should I do?

Thanks for helping me,
S

 

[fluteplr]

Update DTable set Field='Value'
where ID_Field in (SELECT ID_Field from STable where [here comes a long list of conditions that I need to build dynamicaly])

 

[simco]

Oh yes, thank you. I considered this, but (I forgot to say, my mistake), I have a lot of Update (like more than 20) statements that use the same condition (where IF_Field in....). I would like to run the first select only once and then use it for each Update.

 

[checkai]

You could do this:
Update DTable set Field='Value', Field2 ='Value2', Field3='Val3'
where ID_Field in (SELECT ID_Field from STable where [here comes a long list of conditions that I need to build dynamicaly])

or if this doesn't work...you can create a temp table and hold the ID_Field in there...or return from a User Defined Function...

dlc

 

[simco]

Is it possible to use a table type variable to store the result of the first select? If yes, how?

thank you