Can I create temp table and update using temp table in same query?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it possible to create a table (eg. select field1, field2 into TmpTable
from TableX) and,
in this same query,
update another table based on this recently-created table (Update TableY
INNER JOIN TmpTable ON TableY.ID = TmpTable.ID SET TableY.field2 =
TmpTable.field2 WHERE TmpTable.ID = "123") ?

Also, would I be able to create the Temp Table so that it only exists during
this instance of the query execution?

Thanks,
VM
 
What you're trying to do does not require a temp table
(which would also leave "garbage" in your database!).
What you need to do is cretae two queries: a SELECT one to
do the first job, and an UPDATE one based on the former
(rather than a temp table).
Running the latter will automatically run the former in
the background, and do the job in one move.
 
In my case, the temp table is created from a Sybase table that's
ODBC-linked, so I can't update anything if I use these linked tables.
If I incorporate these linked tables into the Update, I get the "Operation
must use an updateable query".

Instead of having a query that creates the table *and* another query that
updates, I'd like to join these two queries into one.

VM
 
Back
Top