CODEWITHSUNDEEP
mysqlsql
camcam
camcam

Reputation: 2625

mysql how to update a table by auto-incrementing a column

A MySql 5.3 table with 100K rows has a primary key.

There is also an integer column which is not part of the key. I would like to update this column to contain a unique number for the table. E.g. for the first record it should contain 1, for the second 2 etc.

This could as well be an auto-increment column, but MySql does not allow auto-increment on non-key columns. I don't want this column to be part of the key, because of the way it gets populated from a file etc.

So how such a query would look like?

Upvotes: 1

Views: 7304

Answers (1)

user1092803
user1092803

Reputation: 3277

I don't know why do you want to do something like this, but a possible solution is this:

set @rownum:=0;
update <table> set column = @rownum:=rownum+1 order by <field>

Upvotes: 10

Related Questions