Reputation: 571
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
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