CODEWITHSUNDEEP
sqloracle-databaseoracle11g
sqlpractice
sqlpractice

Reputation: 141

Re-insert in Table after Selecting records from Same table

I want to fetch records from a table and re-insert those in the same table

Table Data -

Name Address Sub1 Sub2 Type
A Add1 SubA SubO 1
A Add2 SubA SubM 1
B AddB SubB SubO 2
C AddC SubC SubO 2

I have multiple Names(B,C,D.. around 40 ) with Type 2 records and only Name A with Type1.

I want to insert Type2 records with column entries of Type1, meaning the table after my insert should look like this-

Name Address Sub1 Sub2 Type
A Add1 SubA SubO 1
A Add2 SubA SubM 1
B AddB SubB SubO 2
B Add1 SubA SubO 2
B Add2 SubA SubM 2
C AddC SubC SubO 2
C Add1 SubA SubO 2
C Add2 SubA SubM 2

How can I form select from this table and re-insert in the same table?

Upvotes: 0

Views: 856

Answers (1)

MT0
MT0

Reputation: 167991

Use INSERT ... SELECT:

INSERT INTO data (name, address, sub1, sub2, type)
SELECT n.name, d.address, d.sub1, d.sub2, 2
FROM   data d
       CROSS JOIN (
         SELECT DISTINCT
                name
         FROM   data
         WHERE  name <> 'A'
       ) n
WHERE  type = 1;

Which, for the sample data:

CREATE TABLE data (Name, Address, Sub1, Sub2, Type) AS
SELECT 'A', 'Add1', 'SubA', 'SubO', 1 FROM DUAL UNION ALL
SELECT 'A', 'Add2', 'SubA', 'SubM', 1 FROM DUAL UNION ALL
SELECT 'B', 'AddB', 'SubB', 'SubO', 2 FROM DUAL UNION ALL
SELECT 'C', 'AddC', 'SubC', 'SubO', 2 FROM DUAL;

After the INSERT then the table contains:

NAME ADDRESS SUB1 SUB2 TYPE
A Add1 SubA SubO 1
A Add2 SubA SubM 1
B AddB SubB SubO 2
C AddC SubC SubO 2
B Add1 SubA SubO 2
B Add2 SubA SubM 2
C Add1 SubA SubO 2
C Add2 SubA SubM 2

db<>fiddle here

Upvotes: 2

Related Questions