insert data using counters in mysql

Question

I have this data set in MySql:

   p_id  c_id     amount
    5      1      2000
    6      1      3000
    7      1      4000

I need to insert the same data in that table, and just increment the counter of c_id from 1 till 7.

So, in all there will be 21 entries total.

How can I do this with just SQL??

Result:

   p_id  c_id     amount
    5      1      2000
    6      1      3000
    7      1      4000
    5      2      2000
    6      2      3000
    7      2      4000
    5      3      2000
    6      3      3000
    7      3      4000
    5      4      2000
    6      4      3000
    7      4      4000

c_id can go till 9.

can you show the result in table format? – Kaushik Jan 27 '16 at 14:43 — Kaushik, Jan 27 '16 at 14:43
@UnknownUser Added the result. – nirvair Jan 27 '16 at 15:00 — nirvair, Jan 27 '16 at 15:00
You want to insert in same table or different table? – Kaushik Jan 27 '16 at 15:05 — Kaushik, Jan 27 '16 at 15:05
need to insert in same table – nirvair Jan 27 '16 at 15:07 — nirvair, Jan 27 '16 at 15:07

score 0 · Answer 1 · answered Jan 27 '16 at 14:45

Is this what you want?

insert into t(p_id, c_id, amount)
    select t.p_id, c.c_id, t.amount
    from t cross join
         (select 2 as c_id union all select 3 union all select 4 union all
          select 5 union all select 6 union all select 7
         ) c;

score -1 · Answer 2 · edited May 23 '17 at 12:31

-1

What you need is a sequence generator: generate an integer sequence in MySQL

Use any table with 7 or more rows.

INSERT IGNORE INTO original_table
SELECT o.p_id, n.c_id, o.amount
FROM original_table o
JOIN (
  SELECT @row := @row + 1 as c_id
  FROM some_table t, (SELECT @row := 0) r
  LIMIT 7
) n
  ON true

edited May 23 '17 at 12:31

Community

1
1

answered Jan 27 '16 at 14:57

slbteam08

681
3
11

insert data using counters in mysql

2 Answers2