SQL Server Performance

delete with outer join

Discussion in 'General Developer Questions' started by boutwater, Apr 7, 2009.

  1. boutwater Member

    Hello,
    I'm having trouble figuring out what's going on here. If I try to do a select with an outer join on my table, i get 3 million rows back. If I try to do the delete, it doesn't delete any. Here's the script:
    select count(*)
    from boscov.dbo.CartonHistory ch
    right outer join boscov.dbo.CartonArchive ct
    on ch.RecDocID = ct.RecdocID
    and ch.DocType = ct.RecDocType
    and ch.CartonID = ct.CartonID
    and ct.CartonID is null
    and ch.DateTimeStamp < '2008100903000583'
    /*above returns 3 million +
    delete boscov.dbo.CartonHistory
    from boscov.dbo.CartonHistory ch
    right outer join boscov.dbo.CartonArchive ct
    on ch.RecDocID = ct.RecdocID
    and ch.DocType = ct.RecDocType
    and ch.CartonID = ct.CartonID
    and ct.CartonID is null
    and ch.DateTimeStamp < '2008100903000583'
    /*above doesn't delete any
    My goal is to delete the 3 million + and I can't figure out what's wrong with the script. Thanks for any help!
    Ben
  2. Madhivanan Moderator

    delete ch from ....
  3. boutwater Member

    I tried that with the same results. Any other ideas? Thanks,
    Ben
  4. Adriaan New Member

    Your query is a little weird.
    FROM History RIGHT JOIN Archive ON History.id = Archive.id
    WHERE Archive.id IS NULL
    So you're looking for rows in the Archive table where the CartonID is null, and any row in the HistoryTable that matches that CartonID (which is a null).
    If you want to find rows in Archive that have an id that is not in History, then use a LEFT JOIN like this:
    FROM Archive LEFT JOIN History ON Archive.id = History.id
    WHERE History.id IS NULL
    If you want to find rows in History that have an id that is not in Archive, then again use a LEFT JOIN:
    FROM History LEFT JOIN Archive ON History.id = Archive.id
    WHERE Archive.id IS NULL
  5. boutwater Member

    thank you. That was my issue. I was not using the correct join (I forgot how they actually worked). Thanks for reminding me, and for the help. It's working now.

Share This Page