Mike,
see below
On 5 Feb 2003 at 11:36, Mike Starke wrote:
> On Wed, Feb 05, 2003 at 09:18:59AM -0700, az_pete@cactusfamily.com wrote:
> /_Hi Mike,
> /_I think this is what you're looking for.
> /_
> /_
> /_drop table if exists tmp_idt;
> /_
this simply drops the table if it exists, to prevent the create temporary table statement from failing if the table
already exists.
> /_create temporary table tmp_idt
> /_SELECT (idt.unit * idt.price) AS Sub_Total
> /_FROM idt WHERE idt.myid = "1";
> /_
MySQL allows you to create a temp table and populate with the results of a select in one shot. That's what this
statement does. If the 'create temporary table' statement is immediately followed by a select statement, the results
of the select statement are populated into the the table using the columns names & data types returned from the select
statement.
Alternativley you could to this:
create temporary table tmp_idt (
unit int not null,
price int not null
);
insert into tmp_idt
select (idt.unit * idt.price) as sub_total
from idt where idt.myid="1"
But with this method you must explicitly define the table. This can be a pain since you must know exactly the data
types and lengths needed to build the temp table. The advantage to this method is that you can expicitly define
indexes and other constraints on the temp table to increase access speed. When you dynamically create the temp table
(as in the 1st example) *only* the data types are created, there are no indexes put on the temp table even if there
were indexes on the original source tables.
As an aside, if the column idt.myid is an int datatype you don't need to quote it. If it's an integer and you quote
it, MySQL must do an implicit data type conversion, which may slow things down a bit. If the myid column is a varchar
data type then you must quote it.
> /_select sum(Sub_Total) as 'total' from tmp_idt;
> /_
You may now manipulate the tmp_idt table the same as any other table. Note that as soon as you kill your client
connection the temp table is destroyed. If you like, you can explicitly drop the table like this:
drop table tmp_idt;
> /_
> Yes, this works. Could you explain to me how the values
> are inserted into the temporary table? All I see is a 'create table'
> statement. How is the table populated with values to be able to
> issue the second select statement?
>
> Thanks for the solution, now I need to understand how it works :-)_
>
Hope you found this helpful. If you have more questions, let me know.
Peter
> v/r
> -Mike
>
>
> ---------------------------------------------------
> PLUG-discuss mailing list - PLUG-discuss@lists.plug.phoenix.az.us
> To subscribe, unsubscribe, or to change you mail settings:
> http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss
>
>
(text/plain)