MySQL: Sequentially number a column based on change in a different column
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
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
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.
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.
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;
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);
- 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
- I can follow this, but I need to update
line_nowith an actual value. Can this be fashioned into an
UPDATEstatement? Running it as is just returns all rows.
- No joy.
line_nodoes not update.
- @Chris. . . I have a