Mysql: Add Sequence Column Based on Another Field

MySQL: Add sequence column based on another field

This should work but is probably slow:

CREATE temporary table seq ( id int, seq int);
INSERT INTO seq ( id, seq )
    SELECT id, 
      (SELECT count(*) + 1 FROM test c 
      WHERE c.id < test.id AND c.account = test.account) as seq 
    FROM test;

UPDATE test INNER join seq ON test.id = seq.id SET test.seq = seq.seq;

I have called the table 'test'; obviously that needs to be set correctly. You have to use a temporary table because MySQL will not let you use a subselect from the same table you are updating.

Insert sequential numbers based on another field - MySQL

You can do what you want with this code:

set @p := -1;
set @i := 0;

UPDATE my_table t
    SET page_number = (CASE WHEN @p = t.`parent` THEN @i := @i+ 1 
                            WHEN (@p := t.parent) = NULL THEN NULL -- never happens
                            ELSE @i := 1
                       END)
    ORDER BY t.parent;

Unfortunately, MySQL doesn't allow both ORDER BY and JOIN in the same UPDATE query. If it did, you could initialize the variables in the query.

Note the second condition just does the assignment. = NULL never returns TRUE.

Autoincrement MySQL table conditional on other column values

I think I understand what you want. "MOVE NUMBER" should be one higher every time you insert a new move for a certain player "ID" and "Level". Calling this "autoincrementing" is somewhat misleading, because MySQL already has an AUTO_INCREMENT, which is something different.

Let's first get the last "MOVE NUMBER" for a "ID" = 1 and "Level" = 1:

SELECT MAX(`MOVE NUMBER`) FROM GameScore WHERE ID = 1 AND Level = 1;

For the last results in your question this should return 2. However, this could return NULL, so we do:

SELECT IFNULL(MAX(`MOVE NUMBER`), 0) FROM GameScore WHERE ID = 1 AND Level = 1;

then it will return 0.

Now all we need to do is insert a new "VALUE", for instance 463. This goes like this:

INSERT INTO GameScore (ID, 
                       LEVEL, 
                       `MOVE NUMBER`, 
                       VALUE)
SELECT 1,
       1,
       IFNULL(MAX(`MOVE NUMBER`), 0) + 1, 
       463 
FROM GameScore 
WHERE ID = 1 AND 
      Level = 1; 

Please note that queries are untested, they are just given as examples.

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

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;

MySQL update column with sequential numbers grouped by a field with the same value

What I understand is that you need the row number for a group of id_trabalho columns. Since Mysql 5 doesn't have CTE, you could use subqueries to perform the same.

    update sis_coautoria a
join 
(SELECT 
    @row_number:=CASE
        WHEN @id_trabalho = id_trabalho THEN @row_number + 1
        ELSE 1
    END AS rn,
    @id_trabalho:=id_trabalho as id_trabalho,
    id_auto_coautoria
FROM
    sis_coautoria
ORDER BY id_auto_coautoria)
cte on a.id_auto_coautoria=cte.id_auto_coautoria
set a.hierarquia_coautoria=cte.rn

note: I have not run this query on an editor.

Add Sequence with same id mysql

Start by adding the column:

alter table add column seq int;

Then you can do this using variables:

set @rn = 0;
set @p = -1;

update products p
    set seq = (@rn := if(@p = product_id, @rn + 1,
                         if(@p := product_id, 1, 1)
                        )
              )
    order by product_id;

Note that you have no control over the ordering of the rows, because you don't seem to have a field that says which row should be first.

---

Related Topics