mysql - Auto insert rows with repeated data, following two patterns -

i have table looks this:

| id  | letter | number | |-----|--------|--------| | 1   |      | 1      | | 2   | b      | 1      | | 3   | c      | 1      | | 4   | d      | 1      | | 5   |      | 2      | | 6   | b      | 2      | | 7   | c      | 2      | | 8   | d      | 2      | | 9   |      | 3      | | 10  | b      | 3      | | 11  | c      | 3      | | 12  | d      | 3      | |etc..|        |        | 

i'm trying make sql statement auto-fills table following pattern id 456.

so letters abcd abcd until sequence ends, , each 'group' of 4 has number, should reach 114.

i'm not sure best way tackle is, suggestions appreciated.

you can use following sql script insert values required table:

insert target (id, letter, `number`) select rn, col, (rn - 1) % 4 + 1 seq ( select col, @rn := @rn + 1 rn  (    select 'a' col union select 'b' union    select 'c' union select 'd') t cross join (    select 1 x union select 1 union select 1 union     select 1 union select 1 union select 1 union select 1 union    select 1 union select 1 union select 1 union select 1 ) t1 cross join (    select 1 x union select 1 union select 1 union     select 1 union select 1 union select 1 union select 1 union    select 1 union select 1 union select 1 union select 1 ) t2 cross join (select @rn := 0) var  ) s rn <= 456 

the above query creates numbers table of 121 rows using 11 x 11 cartesian product. these rows cross joined in-line table ('a'), ('b'), ('c'), ('d') produce total of 484 rows. outer query selects rows needed, i.e. 456 rows in total.

note: if want insert values:

id, letter, number 1   'a'     1 2   'b'     1 3   'c'     1 4   'd'     1 5   'a'     2 6   'b'     2 7   'c'     2 8   'd'     2 ... etc 

instead of values:

id, letter, number 1   'a'     1 2   'b'     2 3   'c'     3 4   'd'     4 5   'a'     1 6   'b'     2 7   'c'     3 8   'd'     4 ... etc 

then replace (rn - 1) % 4 + 1 seq (rn - 1) div 4 + 1 seq.

demo here