How to update rows with a given repeating number sequence. my table is as follows

Now I want to update the column value with the repeating pattern of 8532 So the table after the update will look like

How can I achieve this in a update statement ?

CodePudding user response：

With the data you have provided it is not possible to satisfy your requirement. The data in a table is not stored in a specific order. If you want the order to be guaranteed in a select statement, you need to provide an ORDER BY clause.

In the code below there is an additional column "ORDER_BY" to specify the order in which the records need to be processed. The repeating pattern is calculated using the MOD function to convert the row number to a repeating sequence of 4 numbers and then CASE maps each of those numbers to its respective pattern location.

WITH test_data (order_by, line_type, line_val)
AS
(
SELECT 1, 'A',1 FROM DUAL UNION ALL 
SELECT 2, 'A',2 FROM DUAL UNION ALL 
SELECT 3, 'B',5 FROM DUAL UNION ALL 
SELECT 4, 'B',6 FROM DUAL UNION ALL 
SELECT 5, 'C',3 FROM DUAL UNION ALL 
SELECT 6, 'C',4 FROM DUAL  
)
SELECT
  CASE MOD(ROW_NUMBER() OVER (ORDER BY order_by),4)
    WHEN 1 THEN 8
    WHEN 2 THEN 5
    WHEN 3 THEN 3
    WHEN 0 THEN 2
  END as line_pattern,
  t.*
  FROM
  test_data t

LINE_PATTERN   ORDER_BY L   LINE_VAL
------------ ---------- - ----------
           8          1 A          1
           5          2 A          2
           3          3 B          5
           2          4 B          6
           8          5 C          3
           5          6 C          4

CodePudding user response：

If you don't care about the order then use this form:

UPDATE mytable
   SET line_pattern =
           CASE MOD (ROWNUM, 4)
               WHEN 1 THEN 8
               WHEN 2 THEN 5
               WHEN 3 THEN 3
               WHEN 0 THEN 2
           END