Using code to increment values in a column

Question

I am trying to use a simple code to increment only the values in the “chat_id” column of a table.

For the table lz_chat_archive_dup1, the column “chat_id” is has empty strings (no values). This is the partial excerpt of the table :

mysql> select chat_id, fullname from lz_chat_archive_dup1 LIMIT 5;
+---------+--------------+
| chat_id | fullname     |
+---------+--------------+
|         | Yw           |
|         | Shah         |
|         | Sunny Duhel  |
|         | Leong Zi Yin |
|         | Mohd Nasir   |
+---------+--------------+
5 rows in set (0.00 sec)

I tried to insert a value for the name “Yw” like this and it worked :

mysql> UPDATE lz_chat_archive_dup1 SET chat_id = '383933' where fullname = 'Yw';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

So now the table is like this :

mysql> select chat_id, fullname from lz_chat_archive_dup1 LIMIT 5;
+---------+--------------+
| chat_id | fullname     |
+---------+--------------+
| 383933  | Yw           |
|         | Shah         |
|         | Sunny Duhel  |
|         | Leong Zi Yin |
|         | Mohd Nasir   |
+---------+--------------+
5 rows in set (0.00 sec)

However, the number of rows in this table is 2589, and for me to do it one by one is tedious and time consuming :

mysql> select count(*) from lz_chat_archive_dup1;
+----------+
| count(*) |
+----------+
|     2589 |
+----------+
1 row in set (0.00 sec)

I thought I could use a code something like this to update/increment only that one column, but I don’t think this is the correct syntax for MySQL. Can you please help to correct the code to customize it to work in my situation :

DECLARE @counter int
SET @counter = 383933
UPDATE #lz_chat_archive_dup1
SET @counter = counter = @counter + 1

So with this code, what I am trying to achieve is increment the chat_id column so that the next value is always 1 integer higher than the previous one. So the first row is 383933, the next one should be 383934, 383935, 383936,…etc etc.

The table has > 2000 rows, so this is an excerpt of it :

mysql> select time, endtime, chat_id from lz_chat_archive_dup1 LIMIT 20;
+------------+------------+---------+
| time       | endtime    | chat_id |
+------------+------------+---------+
| 1594948770 | 1594948928 | 383933  |
| 1594950285 | 1594950542 |         |
| 1594950708 | 1594951085 |         |
| 1594953554 | 1594955581 |         |
| 1594955956 | 1594956551 |         |
| 1595215646 | 1595218410 |         |
| 1595215648 | 1595216044 |         |
| 1595216110 | 1595216138 |         |
| 1595220816 | 1595221144 |         |
| 1595221046 | 1595221584 |         |
| 1595221448 | 1595221505 |         |
| 1595222302 | 1595222653 |         |
| 1595236468 | 1595236848 |         |
| 1595236954 | 1595237033 |         |
| 1595293418 | 1595293589 |         |
| 1595303280 | 1595304388 |         |
| 1595303410 | 1595303822 |         |
| 1595303675 | 1595303986 |         |
| 1595304153 | 1595306613 |         |
| 1595304878 | 1595304995 |         |
+------------+------------+---------+
20 rows in set (0.00 sec)

mysql>

Remembering that rows in a relational database represent unordered sets, define 'next'. — Strawberry, Aug 16 '20 at 08:26

score 0 · Answer 1 · answered Aug 16 '20 at 09:08

Here is an approach using a user variable:

set @rn = 383933;
update #lz_chat_archive_dup1
set chat_id = (select @rn := @rn + 1)
order by name;

This will assign an incrementingn number to each row, following the alphabetical order of name. If there are ties, it is undefined which name will get which number (a reason why you should have a primary key column in your table).

score 0 · Answer 2 · answered Aug 16 '20 at 11:14

Assuming the names are unique, you could use a join:

update lz_chat_archive_dup1 cad join
       (select cad2.*, row_number() over () as seqnum
        from lz_chat_archive_dup1 cad2
       ) cad2
       on cad2.name = cad.name
    set count = seqnum + 383933;

I think this might be the recommended approach in MySQL 8+. (The statement on the deprecation of variables is a little vague on whether it would apply to UPDATE.)

You can also use variables. The problem with your statement is:

SET @counter = counter = @counter + 1

This is not even setting the column in the table! It is setting a variable. Use := to set parameters. And I strongly recommend parentheses. So, you can do:

DECLARE @counter int;
SET @counter = 383933;

UPDATE #lz_chat_archive_dup1
    SET counter = (@counter := @counter + 1);

Or, in a single statement:

UPDATE #lz_chat_archive_dup1 cad CROSS JOIN
       (SELECT @counter := 383933) params
    SET cad.counter = (@counter := @counter + 1);

@Strawberry . . . Somehow, I am missing your point, because that appears to be what the OP is asking for. — Gordon Linoff, Aug 17 '20 at 01:13

score 0 · Answer 3 · answered Aug 16 '20 at 14:03

If you can live with numbers starting from 1, following the alphabetic order of your fullname column, you can try with a helper table to run the update:

CREATE TABLE updtab 
AS
SELECT
  ROW_NUMBER() OVER(ORDER BY fullname) AS chat_id
, fullname
FROM lz_chat_archive_dup1;

Then , run the update:

UPDATE lz_chat_archive_dup1
  SET chat_id = (
    SELECT chat_id 
    FROM updtab 
    WHERE updtab.fullname=lz_chat_archive_dup1.fullname
    )
;

Using code to increment values in a column

3 Answers3