cleaning wrong PK FK

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 youHere are two alternate methods:--Method #1: EXISTS
delete
from Product
where not exists (select * from Report where Report.Report_id = Product.Report_id)

--Method #2: LEFT OUTER JOIN
delete
from Product
left outer join Report on Product.Report_id = Report.Report_id
where Report.Report_id is null
In either method, make sure Report_id is indexed in both tables.|||thank you BlindMan

on the 2nd method i am getting :
Incorrect syntax near the keyword 'left'.|||Post your code.|||that one

delete
from Product
left outer join Report on Product.Report_id = Report.Report_id
where Report.Report_id is null

Incorrect syntax near the keyword 'left'

you said : in either method, make sure Report_id is indexed in both tables.

they are PK to FK but they are not indexed
how can I do it when the tables are allready full

ALTER TABLE create index ?

thank's a lot|||I'm assuming that you defined the PK and FK in your head, but haven't done anything with the database. A PK that doesn't exist using a PRIMARY KEY definition is only a good intention from my perspective. ;) I would suggest using something like:CREATE INDEX dropme01 ON Report (Report_Id)
CREATE INDEX dropme02 ON Product (Report_Id)

DELETE FROM Report
WHERE NOT EXISTS (SELECT *
FROM Product
WHERE Product.Report_Id = Report.Report_Id)

DELETE FROM Product
WHERE NOT EXISTS (SELECT *
FROM Report
WHERE Report.Report_id = Product.Report_Id)

DROP INDEX Report.dropme01
DROP INDEX Product.dropme02

ALTER TABLE Report
ADD CONSTRAINT XPKReport
PRIMARY KEY (Report_Id)

ALTER TABLE Product
ADD CONSTRAINT XPKProduct
PRIMARY KEY (Product_Id)

ALTER TABLE Product
ADD CONSTRAINT XFK01Report
FOREIGN KEY (Report_Id)
REFERENCES Report (Report_Id)-PatP|||Pat FK and PK are allready in the tables|||Pat FK and PK are allready in the tables
Are you having any problem now?|||with your first method it works very well
i was just wondering why it doesnt with the second method

but it works ...

thanks a lot|||Public kya Time pass karne aati hai kya idhar?|||Public kya Time pass karne aati hai kya idhar?

No Hindi man, I think English would be more appropiate to express anything that you post here.

He was telling ," Do the people come here only to pass time?"|||No Hindi man, I think English would be more appropiate to express anything that you post here.

He was telling ," Do the people come here only to pass time?"Lol - well I think we all know the answer to that.

Joydeep - you are becoming the SQL Server Forum Official Translator (Asian Languages Division) :)|||Lol - well I think we all know the answer to that.

Joydeep - you are becoming the SQL Server Forum Official Translator (Asian Languages Division) :)
........;)|||Sorry. There was a syntax error in my second example. This should work:
delete Product
from Product
left outer join Report on Product.Report_id = Report.Report_id
where Report.Report_id is null|||i try it thank you