MySQL create temporary table with autoincrement from SELECT only

Question

I also know it's possible to create autoincrement in a temporary table: Auto Increment in Temporary Table

I want to do both at the same time: create a temporary table from a SELECT statement and I want the temporary table to have an auto increment column.

It is possible to do that?

Thank you but I must create the temporary table from the select itself (no create temporary table before) — evg02gsa3, Nov 06 '14 at 20:08

score 3 · Accepted Answer · answered Nov 06 '14 at 20:07

If you want to create a temporary table with a row number column, then use variables:

create temporary table temp as
    select (@rn := @rn + 1) as seqnum, t.*
    from t cross join
         (select @rn := 0) vars;

This will not be auto-incrementing for new inserts. If you need that, you'll need to alter the table definition.

1 Answers1