Re: Tricky INSERT

[Brent Baisley]

You can't insert select into a table you select from. So yes, you would 
need to create a temp table first and them insert select from that.

On Tuesday, August 12, 2003, at 07:30 PM, Fraser Hanson wrote:

I have table A with column id, and table B with columns id and
content.
I am trying to build a query that inserts all ids from table A into
table B, and also sets the "value" field for all these new entries to
a given value.
For example:
#Before the query, the tables are like this:
mysql> select * from a;
    +------+
    | id   |
    +------+
    | hal  |
    | ron  |
    | kip  |
    | dag  |
    | bob  |
    | max  |
    +------+
mysql> select * from b;
    +------+-------+
    | id   | value |
    +------+-------+
    | bob  |  4    |
    | bob  |  5    |
    | max  |  5    |
    +------+-------+
# After the query,  b is like this:

mysql> select * from b;
    +------+-------+
    | id   | value |
    +------+-------+
    | bob  |  4    |
    | bob  |  5    |
    | dag  |  4    |
    | hal  |  4    |
    | kip  |  4    |
    | max  |  4    |
    | max  |  5    |
    | ron  |  4    |
    +------+-------+
I have been trying variations on the INSERT ... SELECT syntax, but
I suspect this is a dead end.  The next best thing I can think
of is building a temporary table that has the values that I want, and
then using INSERT ... SELECT to copy them all into table b.
Can anyone suggest a way to achieve this?
--Fraser Hanson
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    
http://lists.mysql.com/[EMAIL PROTECTED]


--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]