Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2004
    Posts
    8

    Unanswered: Delete matching records between 2 tables

    All,

    Due to the [flawed] design of our system, I don't have primary keys to work with which makes my problem a little more difficult. I have table A which has a subset of records of table B and I would like to delete all the records in table B that are contained within table A.

    Kind of like this...
    DELETE table B WHERE EXISTS (SELECT * FROM table A)

    However as you can see... that won't work. Any suggestions at all will be much appreciated. I've been scratching my head over this problem for days (and I'm just about out of hair).

    Thanks!

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Here's one example. Test it out and make sure you have a good backup before you try it for real. I'm not really considering what you want to do about rows with nulls (if any). This DELETE will ignore them.

    DELETE FROM B
    WHERE EXISTS
    (SELECT *
    FROM A
    WHERE A.col1 = B.col1
    AND A.col2 = B.col2
    AND A.col3 = B.col3);

  3. #3
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    When you've done that, create some KEYS in your database!

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Anyone want to jump in and say


    BACKUP the DB FIRST!
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Feb 2004
    Posts
    8
    Quote Originally Posted by dportas

    DELETE FROM B
    WHERE EXISTS
    (SELECT *
    FROM A
    WHERE A.col1 = B.col1
    AND A.col2 = B.col2
    AND A.col3 = B.col3);
    I actually tried that as well.... (and here's the problem - again with this legacy db schema)... one of the columns sometimes doesn't have any values (NULL) so when I compare them I get NULL = NULL which I believe evaluates to FALSE and so no matches are found. Any other ideas? Thanks for your help.

  6. #6
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    DELETE FROM B
    WHERE EXISTS
    (SELECT *
    FROM A
    WHERE (A.col1 = B.col1 OR (A.col1 IS NULL AND B.col1 IS NULL))
    AND (A.col2 = B.col2 OR (A.col2 IS NULL AND B.col2 IS NULL))
    AND (A.col3 = B.col3 OR (A.col3 IS NULL AND B.col3 IS NULL)));

    This may well be painfully s-l-o-w. But then I'm still assuming this is only Step 1. Step 2 is to fix the problem with the design rather than just clean up after it. So hopefully you'll only have to do this once.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    NULL = NULL evaluates to NULL

    Is it really only one column that can contain NULL values?
    If so try
    Code:
    SELECT *
    FROM    tablea
     INNER
      JOIN tableb
        ON Coalesce(tablea.col1, 1) = Coalesce(tableb.col1, 1)
       AND tablea.col2 = tableb.col2
       AND tablea.col3 = tableb.col3
    I've written this as a select so you can verify the results before deciding whether this is correct or not.
    George
    Home | Blog

  8. #8
    Join Date
    Feb 2004
    Posts
    8
    Right now it's only 1 column but I'd like to safeguard against it if possible. Future tables that we will use this on may have this issues as well.

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Well, as dportas mentioned, it's potentionally going to be a bit of a performance killer because it may cause an index scan (or worse a table scan!), but the solutions provided shuold work.

    Personally I advocate m Coalesce() option, but I'm biased
    George
    Home | Blog

  10. #10
    Join Date
    Feb 2004
    Posts
    8
    Ideally, I'd like to have a way that generic in that it doesn't refer to table column names so I can apply it (with little effort) to other tables (which I'm sure have the same problem). Any way to do this?

    Thanks for your help. It does work with you methods.

Posting Permissions

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