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?

score 67 · Accepted Answer · answered May 20 '13 at 07:34

67

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

answered May 20 '13 at 07:34

peterm

91,357
15
148
157

1

Any clues how I could tweak this to prepend a string to the counter? E.g. instead of 1,2,3,4... foo1,foo2,foo3,foo4... – kabadisha Nov 20 '14 at 10:21
3

@kabadisha Just use `CONCAT()` like so `...SET e.seq_no = CONCAT('foo', @n := @n + 1)...` – peterm Nov 21 '14 at 01:28
Thanks! - obvious when you know how :-) I was struggling understanding the syntax in the SET clause. – kabadisha Nov 23 '14 at 00:50
Link is broken. – Fusion Feb 05 '17 at 18:10
attractive and simple solution. Thanks. – Joshy Francis Oct 19 '20 at 05:34

score 16 · Answer 2 · edited Dec 27 '19 at 20:17

16

Simple query would be, just set a variable to some number you want. Then update the column you need by incrementing 1 from that number.

Ex:: Query to update all the rows where a column is null. it'll update each row id by incrementing 1

    SET @a  = 50000835 ;  
    UPDATE `civicrm_contact` SET external_identifier = @a:=@a+1 
    WHERE external_identifier IS NULL;

edited Dec 27 '19 at 20:17

Dharman

30,962
25
85
135

answered Jan 14 '15 at 12:46

Developer

3,857
4
37
47

score 4 · Answer 3 · edited Jun 15 '21 at 06:10

Maybe this will help you...

DELIMITER $$
DROP PROCEDURE IF EXISTS manual_increment_count$$
CREATE PROCEDURE manual_increment_count()
  BEGIN
    DECLARE count INT DEFAULT 0;
    DSELECT COUNT(*) INTO count FROM textile_events;
    WHILE count > 0 DO
      SET count = count + 1;
      update textile_events 
        set seq_no = count  where eid = 'headsup' AND paid = 'paid'; 
    END WHILE;
  END$$
DELIMITER ;
CALL manual_increment_count();

MySQL update a field with an incrementing variable

3 Answers3

Linked