MySQL: Sequentially number a column based on change in a different column

how to update a column with sequence number in mysql
mysql update column increment value
how to auto increment id numbers with letters and numbers in mysql
mysql row number
mysql update row number
create number column in mysql
mysql increment value by 1 in select
increment column value by 1 in mysql

If I have a table with the following columns and values, ordered by parent_id:

id    parent_id    line_no
--    ---------    -------
1     2            
2     2
3     2
4     3
5     4
6     4

And I want to populate line_no with a sequential number that starts over at 1 every time the value of parent_id changes:

id    parent_id    line_no
--    ---------    -------
1     2            1
2     2            2
3     2            3
4     3            1
5     4            1
6     4            2

What would the query or sproc look like?

NOTE: I should point out that I only need to do this once. There's a new function in my PHP code that automatically creates the line_no every time a new record is added. I just need to update the records that already exist.

Most versions of MySQL do not support row_number(). So, you can do this using variables. But you have to be very careful. MySQL does not guarantee the order of evaluation of variables in the select, so a variable should not be assigned an referenced in different expressions.

So:

select t.*,
       (@rn := if(@p = parent_id, @rn + 1,
                  if(@p := parent_id, 1, 1)
                 )
       ) as line_no
from (select t.* from t order by id) t cross join
     (select @p := 0, @rn := 0) params;

The subquery to sort the table may not be necessary. Somewhere around version 5.7, this became necessary when using variables.

EDIT:

Updating with variables is fun. In this case, I would just use subqueries with the above:

update t join
       (select t.*,
               (@rn := if(@p = parent_id, @rn + 1,
                          if(@p := parent_id, 1, 1)
                         )
               ) as new_line_no
        from (select t.* from t order by id) t cross join
             (select @p := 0, @rn := 0) params
       ) tt
       on t.id = tt.id
    set t.line_no = tt.new_line_no;

How to update a MySQL column with ascending numbers , Given the problem you have a new column postion and that column should be updated for all existing rows with ascending numbers. Furthermore these  This MySQL AUTO_INCREMENT example creates a table called contacts which has 4 columns and one primary key: The first column is called contact_id which is created as an INT datatype (maximum 11 digits in length) and can not contain NULL values.

Or, a little more old school...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id SERIAL PRIMARY KEY
,parent_id INT NOT NULL
);

INSERT INTO my_table VALUES
(1,    2), 
(2 ,    2), 
(3 ,    2), 
(4 ,    3), 
(5 ,    4), 
(6 ,    4);

SELECT x.*
     , CASE WHEN @prev = parent_id THEN @i := @i+1 ELSE @i := 1 END i
     , @prev := parent_id prev 
  FROM my_table x
     , (SELECT @prev:=null,@i:=0) vars 
 ORDER 
    BY parent_id,id;
    +----+-----------+------+------+
    | id | parent_id | i    | prev |
    +----+-----------+------+------+
    |  1 |         2 |    1 |    2 |
    |  2 |         2 |    2 |    2 |
    |  3 |         2 |    3 |    2 |
    |  4 |         3 |    1 |    3 |
    |  5 |         4 |    1 |    4 |
    |  6 |         4 |    2 |    4 |
    +----+-----------+------+------+    

Learning MySQL and MariaDB: Heading in the Right Direction with , Heading in the Right Direction with MySQL and MariaDB Russell J.T. Dyer Use single-digit, sequential numbers for the first column, but the following text for the second Change some of the data entered in the previous exercises, using the  MySQL 8.0 Reference Manual / ALTER TABLE changes the structure of a table. For example, you can add or delete columns, create or destroy indexes, change the type of existing columns, or rename columns or the table itself. You can also change characteristics such as the storage engine used for the table or the table comment.

You can use subquery if the row_number() doesn't help :

select t.*, 
       (select count(*)
        from table t1
        where t1.parent_id = t.parent_id and t1.id <= t.id
       ) as line_no
from table t;

Learning PHP, MySQL, and JavaScript: A Step-By-Step Guide to , ALTER operates on an existing table, and can add, change, or delete columns. Our example adds a column named id with the following characteristics: INT column is useful as a key, because you will tend to search for rows based on this a new row is inserted, its id column will automatically be given the next number in  The VARCHAR(10) in the examples can change to be appropriate for your column. VARCHAR is a character string of variable length. The maximum length—in this example it is 10—indicates the maximum number of characters you want to store in the column. VARCHAR(25) could store up to 25 characters.

Working with Sequences | Working with Data in MySQL, Inserting NULL into an AUTO_INCREMENT column causes MySQL to generate the next sequence number automatically and insert that value into the column. records before the digit count for customer ID values changes. a record based on the value of some other PRIMARY KEY or UNIQUE index, the  If you update an AUTO_INCREMENT column to a value that is greater than the existing values in the column, MySQL will use the next number of the last insert sequence number for the next row. For example, if the last insert sequence number is 3, you update it to 10, the sequence number for the new row is 4.

The Definitive Guide to MySQL 5, Second, most other database systems know nothing about ENUM and SET, which could Important Column Attributes and Options MySQL Keyword Meaning NULL The ON UPDATE For TIMESTAMP columns, the current time is stored when changes are AUTO_INCREMENT A sequential number is automatically input. In MySQL you can simultaneously assign to and read from user variables in a SELECT statement. This allows the following method of numbering rows: set @type = ''; set @num = 1; select type, variety, @num := if(@type = type, @num + 1, 1) as row_number, @type := type as dummy from fruits; +--------+------------+------------+--------+ | type | variety | row_number | dummy | +--------+------------+------------+--------+ | apple | fuji | 1 | apple | | apple | gala | 2 | apple | | apple |

MySQL Tutorial :: 7.9 Using AUTO_INCREMENT, You can also explicitly assign 0 to the column to generate sequence numbers When you insert any other value into an AUTO_INCREMENT column, the automatically generated value follows sequentially from the largest column value. depending on the NO_AUTO_VALUE_ON_ZERO SQL mode: Server SQL Modes. select A.number,1-ISNULL(B.number) present from (select 0 number union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) A left join numbers B using (number);

Comments
  • which version of mariadb is being used?
  • 10.1.36, but I will also need to do it in a MySQL environment (15.1).
  • You wouldn't normally stored (easily) derived data
  • We have a specific need for the line number to be stored. Otherwise, I wouldn't bother.
  • That's a feature of the PRIMARY KEY in ENGINE=MyISAM.
  • I can follow this, but I need to update line_no with an actual value. Can this be fashioned into an UPDATE statement? Running it as is just returns all rows.
  • No joy. line_no does not update.
  • @Chris. . . I have a select statement.