Adding and Deleting records in a junction table.

[KitaYama]

OK, a little hard to explain. (And sorry for the lengthy question)
tables :

I have Product1, Product2, Product3 in tblProducts.
I also have 10 processes in tblProcesses.
And this is what I have in the junction table between them :

---------------------------------------------
| ProductsProcessPK | ProductFK  | ProcessFK|
|-------------------|------------|----------|
| 1                 | 1          | 1        |
|-------------------|------------|----------|
| 2                 | 1          | 3        |
|-------------------|------------|----------|
| 3                 | 1          | 5        |
|-------------------|------------|----------|
| 4                 | 2          | 3        |
|-------------------|------------|----------|
| 5                 | 2          | 5        |
|-------------------|------------|----------|
| 6                 | 2          | 7        |
|-------------------|------------|----------|
| 7                 | 3          | 3        |
|-------------------|------------|----------|
| 8                 | 3          | 8        |
---------------------------------------------

Objective :

1. Add all ProcessFKs of Product1 and Product2 in the junction table to Product3 that already doesn't exist. ( Add 1, 5 , 7)
   Why?
   Processes of ProductFK1 --->1 - 3 - 5
   Processes of ProductFK2 ---> 3 - 5 - 7
   Processes of ProductFK3 ---> 3 - 8 ---> (1 - 5 - 7 is not included. Add it)
   
   
2. Delete any ProcessFK for Product3 that is not present for Product1 And product2.
   (Delete 8 Because neither ProductFK1 Nor ProductFK2 has processFK 8)

I have the solution for part 1:

INSERT INTO
    tblProductsProcesses ( ProductFK, ProcessFK )
SELECT
    3 AS AddThis,
    PP.ProcessFK
FROM
    tblProductsProcesses AS PP
WHERE
    PP.ProductFK In (SELECT S.ProductPK FROM tblProducts AS S WHERE S.ProductPK IN (1,2))
GROUP BY
   PP.ProcessFK
HAVING
   PP.ProcessFK Not In (SELECT ProcessFK FROM tblProductsProcesses WHERE ProductFK=3));

My question:

1. Since all tables are very very large, is there any correction needed for first query? (According to my tests it's OK, but people like you may find something unnecessary)
2. I couldn't come up with the delete query. Can you help with a sql that deletes unnecessary (and almost saved) records for Product3?

Thanks for your time and advice.
A test db is attached.

 

[KitaYama]

Just to add, for some specific reasons beyond this discussion, I can not delete all records for Product3 and add new ones.
Thanks again.

 

[arnelgp]

maybe i am wrong, but i think you need two query there.
first thing, you need to have an Compound Index (No dups) on ProductFK + ProcessFK on table tblProductsProcess.

first, for delete:
delete * from tblProductsProcess Where ProductFK = 3 And ProcessFK NOT IN (select ProcessFK From tblProductsProcess Where ProductFK In (1,2));

next, the additional ProcessFK

insert ito tblProductsProcess (ProductFK, ProcessFK) select 3, T.ProcessFK From (select ProcessFK From tblProductsProcess Where productFK =1 Union select ProcessFK From tblProductProcess Where productFK = 2) As T;


Edit:
the last query can be replaced with:

insert ito tblProductsProcess (ProductFK, ProcessFK) select 3, T.ProcessFK From (select ProcessFK From tblProductsProcess Where productFK IN (1,2)) As T;

 

[KitaYama]

@arnelgp
I had a feeling it could be done in one step. But if there's no way, I'm OK with two queries.

Thanks for your help.