enumerate rows of a table into a new column in MySQL

Question

I have a table. I would like to select onto it a column that can serve as an index of sorts.

I have this table, table1:

col 1     col2  col3
a         b     c
d         e     f
g         h     i

I'd like to do:

CREATE TEMPORARY TABLE temp1 AS
SELECT (some way to create a sequence from 1 to the height of the table) AS `seq`
,col1
,col2
,col3
FROM table1;

and get

seq    col 1     col2  col3
1      a         b     c
2      d         e     f
3      g         h     i

I've been googling around and can't seem to come across anything useful. Sorry, I'm also a beginner at MySQL.

score 1 · Accepted Answer · answered Mar 26 '16 at 04:07

1

If you want to use a temp table, you can copy the table definition of table1, but add an autoincrement column.

DROP TEMPORARY TABLE IF EXISTS temp1;
CREATE TEMPORARY TABLE temp1 (
  seq  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  col1 VARCHAR(255),
  col2 VARCHAR(255),
  col3 VARCHAR(255)
) AS
  SELECT NULL seq
    ,col1
    ,col2
    ,col3
  FROM table1
;

SELECT * FROM temp1;

fiddle

answered Mar 26 '16 at 04:07

Paul Spiegel

30,925
5
44
53

This is a great idea, I didn't think of it like that. – Monica Heddneck Mar 26 '16 at 04:40

score 0 · Answer 2 · edited May 23 '17 at 10:28

0

I think these answers should help you:

Copy your table (except create as temporary) as from here: Duplicating a MySQL table, indexes and data

Then write a procedure as described here: How can I loop through all rows of a table? (MySQL)

edited May 23 '17 at 10:28

Community

1
1

answered Mar 26 '16 at 03:27

mattbrosenberg

83
1
7

is this a 'link only answer'? – Monica Heddneck Mar 26 '16 at 04:39

enumerate rows of a table into a new column in MySQL

2 Answers2