Results 1 to 3 of 3

Thread: Delete Query

  1. #1
    Join Date
    Oct 2007
    Posts
    30

    Unanswered: Delete Query

    In my sql the following 2 select statements return records
    select * from ProjectPeriod where ProjectID = 99910012
    select ParentID as projectPeriodID, ProjectID, Period, PeriodFrom, PeriodTo, parentid from ProjectPeriod where ProjectID = 73

    First statement returns

    ProjectPeriodID, field1 field2
    445
    446
    447
    448

    second statement returns
    445
    446
    448

    I am trying to delete the record in the first dataset that is not in the second
    dataset, ie record with the ProjectPeriodID 447

    I have been trying to build a statement with an exists claus in it, but am not getting anywhere with it, I would appreciate any help.

    Many Thanks

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    The following is UNTESTED. Test it out and make sure you have a useable backup before you try it for real!

    DELETE FROM ProjectPeriod
    WHERE ProjectID = 99910012
    AND NOT EXISTS
    (SELECT ParentID as projectPeriodID
    FROM ProjectPeriod t
    WHERE ProjectID = 73
    AND ParentID = ProjectPeriod.ProjectPeriodID);

  3. #3
    Join Date
    Oct 2007
    Posts
    30
    Funny you should say that about testing, I just had a bach at it myself and wiped out all my test data :-)

    However, after re-inputting my test data, Yours works a treat, so thank you very much

    Debbie

Posting Permissions

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