Can you reuse a subquery result set?

[lijil]

Is it possible to somehow store the result of a query in a local variable, and then reference that local variable in further statements such as "UPDATE Foo where Foo.x IN @myResultSet"?

This would need to be ODBC compliant and be possible in plain dynamic(php generated) SQL (no stored procedures, views).

Thank-you

 

[PHV]

What about something like this ?
UPDATE yourTable SET yourColumn = (SELECT .....)
WHERE x IN (SELECT ....)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lijil]

PHV:
The batch will include multiple UPDATE/INSERT statements which will all reference the subquery (IN (SELECT ...)). I'm trying to store that subquery result set as a local variable rather than query it multiple times.

I've just found the table variable feature of SQL Server 2000:

DECLARE @foo TABLE (
bar varchar(50)
)

Does anyone know if this is a standard feature of ANSI SQL or is this Microsoft specific?

 

[PHV]

Are you sure you have to do multiple update/insert ?
Where are the new data coming from ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lijil]

PHV:
Basically, as users check/uncheck items from a group of checkboxes (web form, submission), I need to determine which records in a junction table to either insert or update. No records are actually deleted, just updated and flagged via 'deleteDate' column. But as you can probably guess, there is quite a bit of logic involved to determine whether I need to update or insert reach item/record. There are many records in there, everything is timestamped. I was hoping to store the variable which is the result set of the user's so called current selection. Then various comparisons in SQL (dates, etc.), and execute an appropriate insert/update based on the T-SQL logic.

 

[PHV]

Have you tried to play with a Recordset object ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[juliane26]

How about using a cursor ?
You can open, fetch and update according to the values found, it can even be declared with hold to be kept after committing. Can be a good way to avoid dead locks.
Cursors are ANSI SQL.
You can open a cursor for a statement 'select .. from ..'
and depending of the logic do things like 'update ... where current of ...'; that way you can avoid additional selects and dead locks.

DECLARE GLOBAL TEMPORARY TABLE ... is used for temp tables, and this is also ANSI SQL.

Still, both might have product specific options to handle special requirements for locking or how long the temp table / the cursor exists ( just until next commit or longer ...)



Juliane