List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:November 25 1999 7:22am
Subject:Re: temp tables "#"
View as plain text  
At 8:30 AM +0200 11/25/99, Richard Ellerbrock wrote:
>This refers to the filename created by the CREATE TEMPORARY TABLE command:
>
>-rw-rw----   1 mysql    mysql        8200 Nov 25 08:28 SQL1e4_939_0.MYD
>-rw-rw----   1 mysql    mysql        1024 Nov 25 08:28 SQL1e4_939_0.MYI
>-rw-rw----   1 mysql    mysql        9004 Nov 25 08:28 SQL1e4_939_0.frm


How do those filenames fit the "#sql" mentioned in the change notes?


>--
>Richard Ellerbrock
>richarde@stripped
>
>>>>  Paul DuBois <paul@stripped> 1999/11/24 07:34:51 >>>
>At 11:37 AM -0500 11/24/99, mark@stripped wrote:
>>Thanks Paul,
>>
>>Actually I was referring to the manual. Maybe I am a bit mixed up here.
>>
>>
>>
>>D.1.2 Changes in release 3.23.6
>>
>>-->     Temporary tables now starts with #sql.
>
>I'm not sure what this means, but my *guess* is that it means internal
>temporary tables (that the server creates for its own use).  I just
>tried CREATE TEMPORARY TABLE tbl_name SELECT ... FROM other_table
>and it works, where tbl_name is an ordinary table name like you'd
>normally use.
>
>Did you try the kind of statement I suggested below and find
>it not to work?
>
>>
>>
>>If I am reading this  correctly you could actually do something like:
>>
>>select
>>STA1.AccountID, SS1.StateID, STA1.SIC_ID
>>
>>INTO #temp
>>
>>FROM  SIC_TO_ACCOUNTS STA1,
>>         STATES_SERVED  SS1
>>WHERE
>>       STA1.AccountID  =  SS1.AccountID
>>
>>
>>
>>Which would then select these columns into a temp table names #temp.
>>
>>The second query can the access this data via
>>the #temp alias e.g.,
>>
>>select T1.AccountID
>>              , A1.BusinessName
>>               ,A1.Address
>>               ,A1.City
>>               ,S1.State
>>               ,SIC.Name
>>
>>           FROM #temp T1,
>>                ACCOUNTS A1,
>>                SIC_CODES SIC,
>>                STATES S1
>>
>>           WHERE
>>           T1.StateID = S1.StateID
>>           AND T1.SIC_ID = SIC.SIC_ID
>>           AND A1.AccountID = T1.AccountID
>>
>>           group by SIC.Name, S1.State, A1.BusinessName
>>
>>           HAVING
>>           T1.StateID = S1.StateID
>>           AND T1.SIC_ID = SIC.SIC_ID
>>           AND A1.AccountID = T1.AccountID !;
>>
>>
>>However now that I think about it you are probably right. I seem to
>>remember that mysql uses the
>>/*
>>#
>>and
>>--
>>
>>for comments...
>>
>>
>>But then what is this:
>>-->     Temporary tables now starts with #sql.
>>
>>
>>
>>Thanks,
>>
>>Mark
>>
>>On Wed, Nov 24, 1999 at 10:20:12AM -0600, Paul DuBois wrote:
>>>   At 10:58 AM -0500 11/24/99, mark@stripped wrote:
>>>   >I am using a the newest alpha release (2.23.6) and I  would like to
>>>   >use the new temp table feature. It looks like it is very simmilar to
>>>   >the sybase temp table.
>>>   >
>>>   >
>>>   >So for example, I would like to run the sql below...
>>>   >
>>>   >
>>>   >STA1.AccountID, SS1.StateID, STA1.SIC_ID
>>>   >
>>>   >INTO #temp
>>>   >
>>>   >FROM  SIC_TO_ACCOUNTS STA1,
>>>   >       STATES_SERVED  SS1
>>>   >WHERE
>>>   >     STA1.AccountID  =  SS1.AccountID
>>>
>>>
>>>   "#" is not a legal character in a table name in MySQL.
>>>   In any case, there seems to be something missing at
>>>   the front of your query.
>>>
>>>   I presume you'll want to use something like this:
>>>
>>>   CREATE TEMPORARY TABLE temp SELECT
>>> 	STA1.AccountID, SS1.StateID, STA1.SIC_ID
>>>   FROM  SIC_TO_ACCOUNTS STA1,
>>> 	STATES_SERVED  SS1
>>>   WHERE
>>> 	STA1.AccountID  =  SS1.AccountID
>>>
>>>   --
>>>   Paul DuBois, paul@stripped
>>
>>---------------------------------------------------------------------
>>Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
>>posting. To request this thread, e-mail mysql-thread19544@stripped
>>
>>To unsubscribe, send a message to the address shown in the
>>List-Unsubscribe header of this message. If you cannot see it,
>>e-mail mysql-unsubscribe@stripped instead.
>
>
>--
>Paul DuBois, paul@stripped
>
>---------------------------------------------------------------------
>Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
>posting. To request this thread, e-mail mysql-thread19548@stripped
>
>To unsubscribe, send a message to the address shown in the
>List-Unsubscribe header of this message. If you cannot see it,
>e-mail mysql-unsubscribe@stripped instead.
>
>
>---------------------------------------------------------------------
>Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
>posting. To request this thread, e-mail mysql-thread19577@stripped
>
>To unsubscribe, send a message to the address shown in the
>List-Unsubscribe header of this message. If you cannot see it,
>e-mail mysql-unsubscribe@stripped instead.


-- 
Paul DuBois, paul@stripped
Thread
temp tables "#"mark24 Nov
  • Re: temp tables "#"Paul DuBois24 Nov
    • Re: temp tables "#"mark24 Nov
      • Re: temp tables "#"Paul DuBois24 Nov
      • Re: temp tables "#"Michael Widenius26 Nov
  • Re: temp tables "#"Steve Ruby24 Nov
Re: temp tables "#"Richard Ellerbrock25 Nov
  • Re: temp tables "#"Paul DuBois25 Nov