Results 1 to 15 of 15
  1. #1
    Join Date
    Dec 2005
    Posts
    266

    Unanswered: 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 you

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Here are two alternate methods:
    Code:
    --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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Dec 2005
    Posts
    266
    thank you BlindMan

    on the 2nd method i am getting :
    Incorrect syntax near the keyword 'left'.
    Last edited by anselme; 09-24-06 at 15:12.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Post your code.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Dec 2005
    Posts
    266
    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

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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:
    Code:
    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

  7. #7
    Join Date
    Dec 2005
    Posts
    266
    Pat FK and PK are allready in the tables

  8. #8
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by anselme
    Pat FK and PK are allready in the tables
    Are you having any problem now?
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  9. #9
    Join Date
    Dec 2005
    Posts
    266
    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

  10. #10
    Join Date
    Aug 2004
    Location
    Pune,India
    Posts
    94
    Public kya Time pass karne aati hai kya idhar?
    In GOD we believe. Everything else we Test!

  11. #11
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by wash
    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?"
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by rudra
    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)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by pootle flump
    Lol - well I think we all know the answer to that.

    Joydeep - you are becoming the SQL Server Forum Official Translator (Asian Languages Division)
    ..........
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Sorry. There was a syntax error in my second example. This should work:
    Code:
    delete Product
    from Product
    left outer join Report on Product.Report_id = Report.Report_id
    where Report.Report_id is null
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Dec 2005
    Posts
    266
    i try it thank you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •