CODEWITHSUNDEEP
sqlsql-serverinsertforeign-keys
Soham Shah
Soham Shah

Reputation: 571

SQL - Copying rows of the same table but with different foreign key ID

I have three tables which are interlinked:

Table #1 : MAIN

ID_MAIN  NAME_MAIN  ID_VER
1        XYZ        1.0
2        PQR        1.0
3        ABC        1.0

SUBMAIN: Columns ID_SUBMAIN(identity), NAME_SUBMAIN , ID_MAIN(Foreign Key with MAIN) , ID_VER(Foreign Key with MAIN)

ID_SUBMAIN   NAME_SUBMAIN   ID_MAIN   ID_VER
1            X              1         1.0
2            Y              1         1.0
3            Z              1         1.0
4            A              2         1.0

Table #3 LAST:

Columns ID_LAST(identity) , ID_SUBMAIN(Foreign Key with SUBMAIN)

ID_LAST   ID_SUBMAIN
1         1
2         1
3         1
4         2
5         4

Now whenever I create a new MAIN row with ID_MAIN as 1 and ID_VER as 2.0, I want to copy all of the associated records of SUBMAIN with new ID_MAIN and LAST with the new ID_SUBMAIN.

New record in MAIN

ID_MAIN  NAME_MAIN  ID-VER
1        XYZ        2.0

I am using Insert query to copy all of SUBMAIN records for ID_MAIN = 1

My query is like this:

INSERT INTO SUBMAIN(NAME_SUBMAIN, ID_MAIN, ID_VER) 
   SELECT 
       NAME_SUBMAIN, ID_MAIN, '2.0' 
   WHERE 
       ID_MAIN = 1

SO new records of SUBMAIN will be:

ID_SUBMAIN   NAME_SUBMAIN   ID_MAIN   ID_VER
5            X              1         2.0
6            Y              1         2.0
7            Z              1         2.0

Now I want to copy all of the records of LAST table where ID_SUBMAIN was 1,2 and 3. Replace ID_SUBMAIN with new ID_SUBMAIN 5,6 and 7.

New records in LAST should look like this:

ID_LAST   ID_SUBMAIN
6         5
7         5
8         5
9         6

I am stuck here as I am not able to figure out how can I achieve that?

Upvotes: 3

Views: 2034

Answers (1)

Ismael Fuentes
Ismael Fuentes

Reputation: 250

the sql script you are looking for looks like this one:

INSERT INTO LAST(ID_SUBDOMAIN)
SELECT Sub2.ID_SUBDOMAIN
FROM SUBMAIN AS SUB1
    INNER JOIN LAST ON LAST.ID_SUBMAIN = SUB1.ID_SUBMAIN
    INNER JOIN SUBMAIN AS SUB2 ON SUB2.NAME = SUB1.NAME
        AND SUB2.ID_VER = '2.0'

This way, in the first join you get the submains that are already referenced in the "LAST" table and with the next inner join, you get the new ones whith the same name and new version

Upvotes: 3

Related Questions