cleaning Wrong PK FK

[anselme]

Hello

my 2 tables in MS SQL 2000

Report :
Report_id (PK)
name

Product :
Product_id (PK)
Report_id (FK)
name


the Foreign Key and Primary Key have been added later (when the tables were allready full)
product has a few millions of lines and report a few 10.thousand

now I want to clean the 2 tables and remove all the lines which are not conected by PK > FK or FK > PK


i am trying :

DELETE FROM Product WHERE (Product.Report_id NOT IN (SELECT Report.Report_id FROM Report))

DELETE FROM Report WHERE (Report.Report_id NOT IN (SELECT Product.Report_id FROM Product))

but the database crash : time overflow !

how can I do it ?

thank you

[rmiao]

I'll run select statements first to see how many rows need to be deleted in each of tables.

[anselme]

100 to 1000 only thats not much for that big database, but i need to clean it

[rmiao]

Did you delete rows from smaller table first?

[anselme]

yes

i am getting that code too :

delete
from Product
where not exists (select * from Report where Report.id = Product.Reportid)

[rmiao]

Did you get any error message? Which sql2k service pack does the server have?

[anselme]

with the last code it works , but it is still very long

[mikr0s]

DELETE Product
FROM Product a
LEFT JOIN Report b ON b.Report_id = a.Report_id
WHERE b.Report_id IS NULL

DELETE Report
FROM Report a
LEFT JOIN Product b ON b.Report_id = a.Report_id
WHERE b.Report_id IS NULL

--HTH--

[anselme]

thank you mikros