Query for looping values in column

Question

I need to make a query that moves values of only one column one row up ↑ at a time:

    +------------+----------------+
    | anotherCOL | values_to_loop |
    +------------+----------------+
    |          1 |              1 |
    |          2 |              2 |
    |          3 |              3 |
    |          4 |              4 |
    |          5 |              5 |
    |          6 |              6 |
    |          7 |              7 |
    |          8 |              8 |
    |          9 |              9 |
    |         10 |             10 |
    +------------+----------------+

So, the next time i run the query, it should look like this

    +------------+----------------+
    | anotherCOL | values_to_loop |
    +------------+----------------+
    |          1 |              2 |
    |          2 |              3 |
    |          3 |              4 |
    |          4 |              5 |
    |          5 |              6 |
    |          6 |              7 |
    |          7 |              8 |
    |          8 |              9 |
    |          9 |             10 |
    |         10 |              1 |
    +------------+----------------+

I need to loop the values of only one MYSQL COLUMN, as in move the values one ROW UP ↑ each time I run the query.

Notice: Tables provided are just illustrative, the data is different.

Answer 1

Here's how you can do it within a single UPDATE query:

UPDATE tbl a
INNER JOIN (
    SELECT values_to_loop
    FROM (SELECT * FROM tbl) c
    ORDER BY anotherCOL
    LIMIT 1
) b ON 1 = 1
SET a.values_to_loop = 
    IFNULL(
        (SELECT values_to_loop
         FROM (SELECT * FROM tbl) c
         WHERE c.anotherCOL > a.anotherCOL
         ORDER BY c.anotherCOL
         LIMIT 1),
        b.values_to_loop
    )

It works as follows:

1. Updates all records from tbl
2. Joins with a temporary table to retrieve the top value of values_to_loop (the one that will go to the bottom)
3. Set the new value for values_to_loop to the corresponding value from the next row (c.anotherCOL > a.anotherCOL ... LIMIT 1)

Notes:

• This works even if there are gaps in anotherCOL (eg: 1, 2, 3, 6, 9, 15)
• It is required to use (SELECT * FROM tbl) instead of tbl because you're not allowed to use the table that you're updating in the update query

---

Faster query when there are no gaps in anotherCOL

If there are no gaps for values in anotherCOL you can use the query below that should work quite fast if you have an index on anotherCOL:

UPDATE tbl a
LEFT JOIN tbl b on b.anotherCOL = a.anotherCOL + 1
LEFT JOIN (
    SELECT values_to_loop
    FROM tbl
    WHERE anotherCOL = (select min(anotherCOL) from tbl)
) c ON 1 = 1
SET a.values_to_loop = ifnull(
    b.values_to_loop,
    c.values_to_loop
)

Answer 2

Perhaps these are what you had in mind:

update T
set values_to_loop = mod(values_to_loop, 10) + 1

update T
set values_to_loop =
    coalesce(
        (
            select min(t2.values_to_loop) from T t2
            where t2.values_to_loop > T.values_to_loop
        ),
        (
            select min(values_to_loop) from T
        )
    ) 

Answer 3

I`ve created a sample table and added both a select to get the looped values and update to loop the values in the table. Also, using a @start_value variable to know the "1" which might be other. Try this:

CREATE TEMPORARY TABLE IF NOT EXISTS temp_table 
  (other_col INT, loop_col int);

INSERT INTO temp_table (other_col, loop_col) VALUES (1,1);
INSERT INTO temp_table (other_col, loop_col) VALUES (2,2);
INSERT INTO temp_table (other_col, loop_col) VALUES (3,3);
INSERT INTO temp_table (other_col, loop_col) VALUES (4,4);
INSERT INTO temp_table (other_col, loop_col) VALUES (5,5);


DECLARE start_value INT;
SELECT  start_value = MIN(loop_col) FROM temp_table;

SELECT  T1.other_col, ISNULL(T2.loop_col, start_value)
FROM    temp_table T1
        LEFT JOIN temp_table T2
            ON T1.loop_col = T2.loop_col - 1;


UPDATE T1 SET
    T1.loop_col = ISNULL(T2.loop_col, @start_value)
FROM    temp_table T1
        LEFT JOIN temp_table T2
            ON T1.loop_col = T2.loop_col - 1;

SELECT *
FROM    temp_table;

Let me know if it works for you.

Step by step:

1 - created a temp_table with values 1 to 5

2 - declared a start_value which will keep the lowest value for the column you to need to loop through

3 - select all rows from temp_table self left join with same temp_table. join condition is on loop_col - 1 so it can shift the rows up

4 - the same self left join, but this time update the values in place too.

please note that in case i get a null value, it should be the start_value there, because it cannot match