CODEWITHSUNDEEP
mysqltemporary
Arch
Arch

Reputation: 625

MySQL - Add column to temporary table with current column count

There is MySQL temporary table definition with a predefined structure:

create temporary table TempTableName as
  select tData.id as id,
         tData.name as name
         tData.someData as data,
from SomeTableName tData inner join ...

As a result temp table contains some count of a row with some data.

How It can be modified with additioanl column to store rowNumber ? for example:

id    name    data    num
<id>  <name> <data>   1
<id>  <name> <data>   2
<id>  <name> <data>   3

So new num column should contain the current count row. How it can be modified to be added such counter ?

Upvotes: 1

Views: 981

Answers (3)

Cayman
Cayman

Reputation: 387

If you just want to add counter column then:

create temporary table TempTableName as
  select tData.id as id,
         tData.name as name
         tData.someData as data,
         ROW_NUMBER() OVER (ORDER BY tData.id) num
from SomeTableName tData inner join ...

Upvotes: 2

Christian
Christian

Reputation: 4641

The AUTO_INCREMENT needs to be set with a primary key, so if this is o.k. for you, cou could add 

ALTER TABLE `temporary` ADD `num` INT NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (`num`);

Upvotes: 1

Dmitry Reutov
Dmitry Reutov

Reputation: 3032

using variables

set @num  = 0;

select
   ...
   @num := @num + 1 as row_number,

Upvotes: 0

Related Questions