Set iterative values in rows of a table

I have the following table

id name address empid

1  AA   aa      0
2  BB   bb      0
3  CC   cc      0

I need to write a query to set empid starting from 1. How to write it please. Do i have to use a 开发者_开发技巧stored procedure to that or can do it with a normal query?

Thank You.

Here is a way to do it that utilizes a pretty obscure assignment operator in MySQL. This solution won't skip numbers in the case of gaps in the primary key sequence like some of the other solutions.

set @count = 0;
update test set empid = @count := @count+1;

Here is the proof:

mysql> create table test (
    -> id int unsigned primary key auto_increment,
    -> name varchar(32) not null,
    -> address varchar(32) not null,
    -> empid int unsigned not null default 0
    -> ) engine=innodb;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test (name, address)
    -> values ('AA', 'aa'), ('BB', 'bb'), ('CC', 'cc');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test;
+----+------+---------+-------+
| id | name | address | empid |
+----+------+---------+-------+
|  1 | AA   | aa      |     0 |
|  2 | BB   | bb      |     0 |
|  3 | CC   | cc      |     0 |
+----+------+---------+-------+
3 rows in set (0.00 sec)

mysql> set @count=0;
Query OK, 0 rows affected (0.00 sec)

mysql> update test set empid = @count := @count+1;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from test;
+----+------+---------+-------+
| id | name | address | empid |
+----+------+---------+-------+
|  1 | AA   | aa      |     1 |
|  2 | BB   | bb      |     2 |
|  3 | CC   | cc      |     3 |
+----+------+---------+-------+
3 rows in set (0.00 sec)

If you are looking to put 1 in empid for the first row, 2 for the second, etc. the easiest way would be to use your id field that is already doing this like so:

UPDATE table
SET empid = id

The only thing you need to worry about is missing numbers in the id column. If that would be an issue and you are missing id numbers, you will have to use a different method. To do that, you would need to do something like this:

DECLARE @counter int
SET @counter = 1
UPDATE table
SET @counter = empid = @counter + 1

As @BiggsTRC suggested you can use id to set empid. If not, you can create stored procedure or some PHP code to do that. If your ID is not "AutoIncrement" field, you can consider a new column as autoincrement field and assign that value to emp with update query and later delete that new column. (These are some alternates, you need to choose the best one)

          UPDATE `test` SET `empid`=`id`

But why would you want to do it? It's pretty much definition of redundancy.