CODEWITHSUNDEEP
mysqlsql
codepuppy
codepuppy

Reputation: 1140

Scope of a temporary mysql table or specifying that a table is temporary

In a set of prepared statements I want to pass the keys found in one prepared statement into a later prepared statement.

I can do this using:

PREPARE get_Class_Session FROM
    "CREATE TABLE time_Table_Keys 
             (SELECT time_Table_Key,
                           date,
                           DATE_FORMAT(date, '%W') AS day,
                           slot,
                           class_Session 
                FROM `Time_Table` 
             WHERE (((date >=? && date <= ?) && DAYOFWEEK(date)=? && (slot >=? && slot <=?)) && class_Session ='0')
            )";

Followed by:

PREPARE update_Time_Table FROM
    "UPDATE Time_Table SET class_Session='8888'
      WHERE time_Table_Key IN ( SELECT time_Table_Key FROM time_Table_Keys)";

And lastly:

PREPARE drop_Tmp_Table FROM
    "DROP TABLE time_Table_Keys";

However:

I really want to use CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE but when I attempt to do that the IN clause cannot find the temporary table because of course it doesn't exist when the prepare is parsed.

Is there some way of specifying the scope of a temporary file and or is there someway of telling the prepare that the table in the IN clause is temporary.

Upvotes: 0

Views: 93

Answers (1)

sgeddes
sgeddes

Reputation: 62851

Maybe I'm missing something, but do you even need the temp tables for this particular case? Would something like this work?

UPDATE Time_Table 
SET class_Session='8888'
WHERE 
(
  (
     (date >=? && date <= ?) && 
     DAYOFWEEK(date)=? && 
     (slot >=? && slot <=?)
  ) 
  && class_Session ='0'
)

Here is the SQL Fiddle.

Upvotes: 1

Related Questions