insert into...select using the same table

[Don Vaillancourt]

How can I do the following in MySQL.

insert into table1 (col1, col2, col3)
select col1, col2, somevalue as col3
from table1

MySQL doesn't allow you to select from the same table you are inserting
into.

Thank You

[Bill Karwin]

"Don Vaillancourt" <donv@webimpact.com> wrote in message
news:VBjUf.15449$43.2817@nnrp.ca.mci.com!nnrp1.uun et.ca...

> How can I do the following in MySQL.
>
> insert into table1 (col1, col2, col3)
> select col1, col2, somevalue as col3
> from table1

One way would be to store the result of the select in a temporary table, and
then copy that back to table1.

Regards,
Bill K.

[Don Vaillancourt]

Ok, did some research and got the answer.

It does work on 4.0.14 and higher.



Don Vaillancourt wrote:

> How can I do the following in MySQL.
>
> insert into table1 (col1, col2, col3)
> select col1, col2, somevalue as col3
> from table1
>
> MySQL doesn't allow you to select from the same table you are inserting
> into.
>
> Thank You