MySQL使用递增变量更新字段 [英] MySQL update a field with an incrementing variable

问题描述

我的一个数据库中有一个名为"textile_events"的表.

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'字段是昨天在上表中引入的.

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

现在，我需要为所有"headsup"的"seq_no"字段分配一个递增编号 付费字段等于付费"的事件.

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;

如何将递增编号分配给仅引入到Recod的新引入字段 满足给定条件?

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

有哪些可用选项，最有效的方法是什么?

推荐答案

您是否正在寻找类似的东西?

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

SQLFiddle

这篇关于MySQL使用递增变量更新字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！