I have to do the following and can't figure out how to do it all correctly:
I have a table test
| Test1 | Test2 |
|---|---|
| 1 | ABC |
| 2 | DEF |
I want to duplicate this, and have the test1 column have subsequent numbering. When I do a simple insert I can of course just duplicate it all and I have what I need. But I can't get the numbering of Test1 right.
The result I'm looking for is this:
| Test1 | Test2 |
|---|---|
| 1 | ABC |
| 2 | DEF |
| 3 | ABC |
| 4 | DEF |
What I'm getting at the moment is:
| Test1 | Test2 |
|---|---|
| 1 | ABC |
| 2 | DEF |
| 1 | ABC |
| 2 | DEF |
I tried the following but did not get subsequent numbering in the test1 column:
INSERT INTO test (test1,
test2)
SELECT test,
test
FROM test; drop table if exists temp.tmp;
create temporary table tmp as
select test1, row_number() over (order by test1) rn
from test; update test
set test1 = (
select rn from temp.tmp
where temp.tmp.test1 = test.test1
);
drop table temp.tmp;
CodePudding user response：
You can use a window function for this task. Specifically, you can duplicate all your rows, yet increasing the Test1 values, by adding the current total count of records to every record of your table.
INSERT INTO tab
SELECT Test1 COUNT(*) OVER(), Test2 FROM tab;
Output:
| TEST1 | TEST2 |
|---|---|
| 1 | ABC |
| 2 | DEF |
| 3 | ABC |
| 4 | DEF |
Check the demo here.
CodePudding user response：
How about this?
Sample data:
SQL> select * from test;
TEST1 TES
---------- ---
1 ABC
2 DEF
5 GHI
Insert duplicates into test2 column:
SQL> insert into test (test2) select test2 from test;
3 rows created.
Fix numbering:
SQL> update test set test1 = rownum;
6 rows updated.
Result:
SQL> select * from test;
TEST1 TES
---------- ---
1 ABC
2 DEF
3 GHI
4 ABC
5 DEF
6 GHI
6 rows selected.
SQL>
CodePudding user response：
Creating an identical table which contains an IDENTITY column would be straightforward I think, provided that the DB's version is 12c , such as
CREATE TABLE Test_( Test1 INT GENERATED ALWAYS AS IDENTITY, Test2 VARCHAR2(99) );
INSERT INTO Test_(Test2) SELECT Test2 FROM Test ORDER BY Test1; -- run as many times as you wish
DROP TABLE Test;
ALTER TABLE Test_ RENAME TO Test;
SELECT * FROM Test; -- assuming the INSERT Statement above run twice
TEST1 | TEST2
------|-------
1 | ABC
2 | DEF
3 | ABC
4 | DEF