I want to find rows (records) which have a specific vlaue (S) in a column (Type), and insert multiple rows (e.g. 2) based on that row in the same table.
For example, in tabe t1 below, I want for each row of type 'S', 2 rows be inserted with same ID and Price, new Counter value (no specific requirement for this filed, however the Counter for records with same ID must be different), and Type will be 'B'.
It means that when inserting 2 rows based on the first record in table below (1,1200,S,200), Counter value of the new records must be different from Counter values of the records with ID=1 already in the table (1200 and 1201). So, in the initial table there were three records with Type 'S', then in the final table, for each of those records, 2 new records with Type 'B' and a new Counter value is insrted:
ID Counter Type Price ------------------------ 1 1200 S 200 1 1201 T 400 2 1200 T 500 3 1546 S 100 3 1547 S 70 4 2607 M 250
The output table t1 will be:
ID Counter Type Price ------------------------ 1 1200 S 200 1 1202 B 200 1 1203 B 200 1 1201 T 400 2 1200 T 500 3 1546 S 100 3 1548 B 100 3 1549 B 100 3 1547 S 700 3 1550 B 700 3 1552 B 700 4 2607 M 250
You just have to play twice this command:
insert into epn
with w(max) as
(
select max(t.counter) from t -- useful to get max counter value
)
select t.id, w.max + rownum, 'B', t.price -- max + rownum then gives new values
from t, w
where t.type != 'B'; -- Avoid duplicating rows added 1st time
This gives:
1 1 1200 S 200
2 1 2617 B 200
3 1 2611 B 200
4 1 1201 T 400
5 1 2618 B 400
6 1 2612 B 400
7 2 1200 T 500
8 2 2613 B 500
9 2 2619 B 500
10 3 1547 S 70
11 3 2609 B 70
12 3 2615 B 70
13 3 1546 S 100
14 3 2614 B 100
15 3 2608 B 100
16 4 2607 M 250
17 4 2610 B 250
18 4 2616 B 250