I can easily visualize this with the assistance of PHP, Perl, or
Python. Yet I am not exactly sure what I am looking for in the MySQL
manual.

Basically, I wish to store the result from a SELECT so that I can use
it or information based on it to insert into another table. Is that
possible with MySQL so I do not have to reinvent the wheel in the
application server itself?

This pertains to my original table example of fruits being associated
with other fruits.

Message-ID: <WvCdnQOuIt7YO2vbnZ2dnUVZ_q_inZ2d@comcast.com>

I thought the query cache was the place to look, but evidently not.
And I cannot seem to locate information for "result" or "result set"
very effectively. I am looking in the CHM version of the latest 5.1
manual if that makes any difference.

Thanks.

--
-Lost
Remove the extra words to reply by e-mail. Don't e-mail me. I am
kidding. No I am not.

>Basically, I wish to store the result from a SELECT so that I can use
>it or information based on it to insert into another table. Is that
>possible with MySQL so I do not have to reinvent the wheel in the
>application server itself?

You can do it with CREATE [TEMPORARY] TABLE used with a SELECT statement
to populate it.

CREATE TEMPORARY TABLE frootlist (frootid int not null
auto_increment primary key, frootname varchar(30)) SELECT
null as frootid, distinct fruit from fruitbasket_contents;

(Checked for syntax running it on MySQL 5.0.45, not checked for results.)

>This pertains to my original table example of fruits being associated
>with other fruits.
>
>Message-ID: <WvCdnQOuIt7YO2vbnZ2dnUVZ_q_inZ2d@comcast.com>
>
>I thought the query cache was the place to look, but evidently not.

As I understand it, the query cache is not particularly visible
except for performance and commands that configure it.

>And I cannot seem to locate information for "result" or "result set"
>very effectively. I am looking in the CHM version of the latest 5.1
>manual if that makes any difference.

Response from gordonb.vt4bk@burditt.org (Gordon Burditt):

>>Basically, I wish to store the result from a SELECT so that I can
>>use it or information based on it to insert into another table.
>>Is that possible with MySQL so I do not have to reinvent the wheel
>>in the application server itself?
>
> You can do it with CREATE [TEMPORARY] TABLE used with a SELECT
> statement to populate it.
>
> CREATE TEMPORARY TABLE frootlist (frootid int not null
> auto_increment primary key, frootname varchar(30)) SELECT
> null as frootid, distinct fruit from fruitbasket_contents;
>
> (Checked for syntax running it on MySQL 5.0.45, not checked for
> results.)

Marvelous! I had not even thought in this direction at all. Thanks!

>>This pertains to my original table example of fruits being
>>associated with other fruits.
>>
>>Message-ID: <WvCdnQOuIt7YO2vbnZ2dnUVZ_q_inZ2d@comcast.com>
>>
>>I thought the query cache was the place to look, but evidently
>>not.
>
> As I understand it, the query cache is not particularly visible
> except for performance and commands that configure it.

Ah, OK. And there does not seem to be any functions that directly
use data stored in the cache. I was thinking since the query cache
utilizes temporary tables that it might be possible to extract said
results.

Ah well, creating my own temporary table is basically the same thing.

Thanks again for pointing that out!

--
-Lost
Remove the extra words to reply by e-mail. Don't e-mail me. I am
kidding. No I am not.

On 24 Sep, 02:15, "-Lost" <maventheextrawo...@techie.com> wrote:
> I can easily visualize this with the assistance of PHP, Perl, or
> Python. Yet I am not exactly sure what I am looking for in the MySQL
> manual.
>
> Basically, I wish to store the result from a SELECT so that I can use
> it or information based on it to insert into another table. Is that
> possible with MySQL so I do not have to reinvent the wheel in the
> application server itself?
>
> This pertains to my original table example of fruits being associated
> with other fruits.
>
> Message-ID: <WvCdnQOuIt7YO2vbnZ2dnUVZ_q_inZ2d@comcast.com>
>
> I thought the query cache was the place to look, but evidently not.
> And I cannot seem to locate information for "result" or "result set"
> very effectively. I am looking in the CHM version of the latest 5.1
> manual if that makes any difference.
>
> Thanks.
>
> --
> -Lost
> Remove the extra words to reply by e-mail. Don't e-mail me. I am
> kidding. No I am not.

INSERT ... SELECT
http://dev.mysql.com/doc/refman/5.0/...rt-select.html

Response from Captain Paralytic <paul_lautman@yahoo.com>:

> On 24 Sep, 02:15, "-Lost" <maventheextrawo...@techie.com> wrote:
>> I can easily visualize this with the assistance of PHP, Perl, or
>> Python. Yet I am not exactly sure what I am looking for in the
>> MySQL manual.
>>
>> Basically, I wish to store the result from a SELECT so that I can
>> use it or information based on it to insert into another table.
>> Is that possible with MySQL so I do not have to reinvent the
>> wheel in the application server itself?
>>
>> This pertains to my original table example of fruits being
>> associated with other fruits.
>>
>> Message-ID: <WvCdnQOuIt7YO2vbnZ2dnUVZ_q_inZ2d@comcast.com>
>>
>> I thought the query cache was the place to look, but evidently
>> not. And I cannot seem to locate information for "result" or
>> "result set" very effectively. I am looking in the CHM version
>> of the latest 5.1 manual if that makes any difference.
>
> INSERT ... SELECT
> http://dev.mysql.com/doc/refman/5.0/...rt-select.html

Ooo, that's nice! Thanks, Captain Paralytic.

To make sure I understand it correctly, I actually need to specify a
target table name in the FROM clause to get it to create that
temporary table?

INSERT INTO tbl_temp2 (fld_id)
SELECT tbl_temp1.fld_order_id
-- putting tbl_temp2 below is what creates the temporary table?
FROM tbl_temp1, tbl_temp2 WHERE tbl_temp1.fld_order_id > 100;

I gave it a quick test, and must be screwing something up because I
get tbl_temp2 does not exist.

Thanks for the help.

--
-Lost
Remove the extra words to reply by e-mail. Don't e-mail me. I am
kidding. No I am not.

On 24 Sep, 10:41, "-Lost" <maventheextrawo...@techie.com> wrote:
> Response from Captain Paralytic <paul_laut...@yahoo.com>:
>
>
>
>
>
> > On 24 Sep, 02:15, "-Lost" <maventheextrawo...@techie.com> wrote:
> >> I can easily visualize this with the assistance of PHP, Perl, or
> >> Python. Yet I am not exactly sure what I am looking for in the
> >> MySQL manual.
>
> >> Basically, I wish to store the result from a SELECT so that I can
> >> use it or information based on it to insert into another table.
> >> Is that possible with MySQL so I do not have to reinvent the
> >> wheel in the application server itself?
>
> >> This pertains to my original table example of fruits being
> >> associated with other fruits.
>
> >> Message-ID: <WvCdnQOuIt7YO2vbnZ2dnUVZ_q_in...@comcast.com>
>
> >> I thought the query cache was the place to look, but evidently
> >> not. And I cannot seem to locate information for "result" or
> >> "result set" very effectively. I am looking in the CHM version
> >> of the latest 5.1 manual if that makes any difference.
>
> > INSERT ... SELECT
> >http://dev.mysql.com/doc/refman/5.0/...rt-select.html
>
> Ooo, that's nice! Thanks, Captain Paralytic.
>
> To make sure I understand it correctly, I actually need to specify a
> target table name in the FROM clause to get it to create that
> temporary table?
>
> INSERT INTO tbl_temp2 (fld_id)
> SELECT tbl_temp1.fld_order_id
> -- putting tbl_temp2 below is what creates the temporary table?
> FROM tbl_temp1, tbl_temp2 WHERE tbl_temp1.fld_order_id > 100;
>
> I gave it a quick test, and must be screwing something up because I
> get tbl_temp2 does not exist.
>
> Thanks for the help.
>
> --
> -Lost
> Remove the extra words to reply by e-mail. Don't e-mail me. I am
> kidding. No I am not.- Hide quoted text -
>
> - Show quoted text -

This doesn't use a temporary table.

INSERT INTO tbl_temp2 (fld_id)
SELECT tbl_temp1.fld_order_id
FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

Response from Captain Paralytic <paul_lautman@yahoo.com>:

<snip>

>> > INSERT ... SELECT
>> >http://dev.mysql.com/doc/refman/5.0/...rt-select.html
>>
>> Ooo, that's nice! Thanks, Captain Paralytic.
>>
>> To make sure I understand it correctly, I actually need to
>> specify a target table name in the FROM clause to get it to
>> create that temporary table?
>>
>> INSERT INTO tbl_temp2 (fld_id)
>> SELECT tbl_temp1.fld_order_id
>> -- putting tbl_temp2 below is what creates the temporary table?
>> FROM tbl_temp1, tbl_temp2 WHERE tbl_temp1.fld_order_id > 100;
>>
>> I gave it a quick test, and must be screwing something up because
>> I get tbl_temp2 does not exist.
>>
>> Thanks for the help.
>>
>
> This doesn't use a temporary table.
>
> INSERT INTO tbl_temp2 (fld_id)
> SELECT tbl_temp1.fld_order_id
> FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

Oh, OK. I must have misunderstood then. What does:

"The target table of the INSERT statement may appear in the FROM
clause of the SELECT part of the query. (This was not possible in
some older versions of MySQL.) In this case, MySQL creates a
temporary table to hold the rows from the SELECT and then inserts
those rows into the target table."

....pertain to?

Thanks again for your help, I appreciate it.

--
-Lost
Remove the extra words to reply by e-mail. Don't e-mail me. I am
kidding. No I am not.

-Lost wrote:
> Response from Captain Paralytic <paul_lautman@yahoo.com>:
>
> <snip>
>
>>>> INSERT ... SELECT
>>>> http://dev.mysql.com/doc/refman/5.0/...rt-select.html
>>> Ooo, that's nice! Thanks, Captain Paralytic.
>>>
>>> To make sure I understand it correctly, I actually need to
>>> specify a target table name in the FROM clause to get it to
>>> create that temporary table?
>>>
>>> INSERT INTO tbl_temp2 (fld_id)
>>> SELECT tbl_temp1.fld_order_id
>>> -- putting tbl_temp2 below is what creates the temporary table?
>>> FROM tbl_temp1, tbl_temp2 WHERE tbl_temp1.fld_order_id > 100;
>>>
>>> I gave it a quick test, and must be screwing something up because
>>> I get tbl_temp2 does not exist.
>>>
>>> Thanks for the help.
>>>
>> This doesn't use a temporary table.
>>
>> INSERT INTO tbl_temp2 (fld_id)
>> SELECT tbl_temp1.fld_order_id
>> FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;
>
> Oh, OK. I must have misunderstood then. What does:
>
> "The target table of the INSERT statement may appear in the FROM
> clause of the SELECT part of the query. (This was not possible in
> some older versions of MySQL.) In this case, MySQL creates a
> temporary table to hold the rows from the SELECT and then inserts
> those rows into the target table."
>
> ...pertain to?
>
> Thanks again for your help, I appreciate it.
>

In previous versions of MySQL it was not possible to insert rows into the same
table they were being selected from. Now it is.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Response from Jerry Stuckle <jstucklex@attglobal.net>:

<snip>

>>> This doesn't use a temporary table.
>>>
>>> INSERT INTO tbl_temp2 (fld_id)
>>> SELECT tbl_temp1.fld_order_id
>>> FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;
>>
>> Oh, OK. I must have misunderstood then. What does:
>>
>> "The target table of the INSERT statement may appear in the FROM
>> clause of the SELECT part of the query. (This was not possible in
>> some older versions of MySQL.) In this case, MySQL creates a
>> temporary table to hold the rows from the SELECT and then inserts
>> those rows into the target table."
>>
>> ...pertain to?
>>
>> Thanks again for your help, I appreciate it.
>
> In previous versions of MySQL it was not possible to insert rows
> into the same table they were being selected from. Now it is.

Oh, well, specifically the "In this case..." part. Is it actually
stating that a temporary table (that you can use) is created?

--
-Lost
Remove the extra words to reply by e-mail. Don't e-mail me. I am
kidding. No I am not.

-Lost wrote:
> Response from Jerry Stuckle <jstucklex@attglobal.net>:
>
> <snip>
>
>>>> This doesn't use a temporary table.
>>>>
>>>> INSERT INTO tbl_temp2 (fld_id)
>>>> SELECT tbl_temp1.fld_order_id
>>>> FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;
>>> Oh, OK. I must have misunderstood then. What does:
>>>
>>> "The target table of the INSERT statement may appear in the FROM
>>> clause of the SELECT part of the query. (This was not possible in
>>> some older versions of MySQL.) In this case, MySQL creates a
>>> temporary table to hold the rows from the SELECT and then inserts
>>> those rows into the target table."
>>>
>>> ...pertain to?
>>>
>>> Thanks again for your help, I appreciate it.
>> In previous versions of MySQL it was not possible to insert rows
>> into the same table they were being selected from. Now it is.
>
> Oh, well, specifically the "In this case..." part. Is it actually
> stating that a temporary table (that you can use) is created?
>

A temporary table is created for the insert, but I don't think you can otherwise
use it. I could be wrong, though.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================