Update column with values of array sequentially-CodePudding

I have a table to which I added a new column 'color'. Now I would like to update all the existing rows to have a one of three colors ('#F00', '#0F0', '#00F') and sequentially.

So first row would be #F00, second #0F0, third #00F and fourth again #F00 etc.

Something like this:

UPDATE table_name SET color = oneOf(['#F00', '#0F0', '#00F']);

How to achieve it?

CodePudding user response：

If your MariaDB version support window function, you can try to use an order column (in my sample code is ID column) that represents the order for your update sequentially

Then use CASE WHEN expression & MOD function to judgment update logic

UPDATE
    table_name AS t
INNER JOIN
    ( 
      SELECT Id, 
             row_number() OVER (ORDER BY Id) AS rn 
      FROM table_name
    ) AS sub
ON  t.Id = sub.Id
SET color = (CASE WHEN MOD(rn,3) = 1 THEN '#F00'
                 WHEN MOD(rn,3) = 2 THEN '#0F0'
                 ELSE '#00F' END)

sqlfiddle