How to autofill a column with sequential number, but based on a parameter?-CodePudding

I'm trying to add a new column to few MySQL (MariaDB) tables. I want to make the column auto-filled with sequential number. What I figured out so far is:

SELECT @count:=0;
UPDATE users SET ordering = @count:=@count 1;

It works perfectly. However, I don't know how to make it so that the order in which the numbers are assigned is based on another value, namely ascending order of another integer field called regdate. Here's an example.

Current result:

login	regdate	ordering
user	1633205589	1
guy	16332060000	3
account	16332090000	2
data	16332095000	4

What I want:

login	regdate	ordering
user	1633205589	1
guy	16332060000	2
account	16332090000	3
data	16332095000	4

I hope it's pretty clear and concise :)

CodePudding user response：

As long as your update just involves a single table, you can specify an order:

UPDATE users SET ordering = @count:=@count 1 ORDER BY regdate;

CodePudding user response：

You can use a joined table with ROW_NUMBER

CREATE TABLE users (
  `login` VARCHAR(7),
  `regdate` VARCHAR(20) ,
  `ordering` INTEGER
);

INSERT INTO users
  (`login`, `regdate`, `ordering`)
VALUES
  ('user', '1633205589', '1'),
  ('guy', '16332060000', '3'),
  ('account', '16332090000', '2'),
  ('data', '16332095000', '4');

UPDATE users  u1
JOIN (SELECT `login`, `regdate`, row_number() over (ORDER BY regdate ASC) rn FROM users) u2 
ON u1.`login` = u2.`login` AND u1.`regdate` = u2.`regdate`
SET u1.ordering =  u2.rn ;

SELECT * FROM users

login   | regdate     | ordering
:------ | :---------- | -------:
user    | 1633205589  |        1
guy     | 16332060000 |        2
account | 16332090000 |        3
data    | 16332095000 |        4

db<>fiddle here