MySQL update a field with an incrementing variable

Question

I have a table named 'textile_events' in one of my databases.

mysql> describe textile_events;  +-------------+--------------+-----+---------+----------------+ | Field       | Type         | Key | Default | Extra          | +-------------+--------------+-----+---------+----------------+ | id          | int(11)      | PRI | NULL    | auto_increment | | token       | varchar(20)  |     | NULL    |                | | reg_time    | datetime     |     | NULL    |                | | eid         | varchar(20)  |     | NULL    |                | | fname       | varchar(20)  |     | NULL    |                | | lname       | varchar(20)  |     | NULL    |                | | paid        | varchar(10)  |     | NULL    |                | | seq_no      | int(11)      |     | NULL    |                | +-------------+--------------+-----+---------+----------------+ 8 rows in set (0.00 sec)  mysql> select count(*) from textile_events;  +----------+ | count(*) | +----------+ |     9325 | +----------+ 1 row in set (0.00 sec)  mysql> select count(*) from textile_events where eid = 'headsup' ;  +----------+ | count(*) | +----------+ |     2553 | +----------+ 1 row in set (0.01 sec) 

'seq_no' field was introduced to the above table yesterday.

Now, I need to assign an incrementing number to 'seq_no' field for all 'headsup' events where paid field is equal to 'paid'.

In other way, I am looking for something like this,

$i = 250 while( true ):     $i++     UPDATE textile_events SET 'seq_no' = $i          WHERE eid = 'headsup' AND paid = 'paid' endwhile; 

How do I assign an incrementing number to a newly introduced field only to recods that satify a given condition?

What are the available options and what is the most efficient way to accomplish this?

Answer 1

Are you looking for something like this?

UPDATE textile_events e,        (SELECT @n := 249) m    SET e.seq_no = @n := @n + 1      WHERE e.eid = 'headsup' AND e.paid = 'paid' 

SQLFiddle