Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2008
    Location
    Denver, CO
    Posts
    44

    Unanswered: DELETE based on composite key?

    I have a table called INVOICE with a composite key made up of InvoiceYear INT and InvoiceId VARCHAR(4) attributes. I want to delete from the table any records which are not foreign key referenced in another table called PAYMENT. This is what I have so far:

    Code:
    DELETE FROM ACCOUNTING.INVOICE WHERE (InvoiceYear NOT IN 
    (SELECT InvoiceYear FROM ACCOUNTING.PAYMENT) AND InvoiceId NOT IN 
    (SELECT InvoiceId FROM ACCOUNTING.PAYMENT))
    It appears to be working, but I'm concerned that it won't behave how I want. I want it to only delete a record if both InvoiceYear and InvoiceId are not referenced (in the same record) in the PAYMENT table (if that makes sense). Does it look like it is working that way?
    Last edited by ppalubinski; 07-19-10 at 12:35.
    Paul Palubinski

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by ppalubinski View Post
    Does it look like it is working that way?
    Nope!

    The good news is you are not deleting rows you want to keep.

    You should use NOT EXISTS or a LEFT OUTER JOIN with a test for NULL. I prefer NOT EXISTS myself.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Oct 2008
    Location
    Denver, CO
    Posts
    44
    Quote Originally Posted by pootle flump View Post
    Nope!

    The good news is you are not deleting rows you want to keep.

    You should use NOT EXISTS or a LEFT OUTER JOIN with a test for NULL. I prefer NOT EXISTS myself.
    Thanks for the reply. So would I have to do something like this?

    Code:
    DELETE FROM ACCOUNTING.INVOICE WHERE NOT EXISTS
    (SELECT * FROM ACCOUNTING.PAYMENT pa 
    WHERE ACCOUNTING.INVOICE.InvoiceYear = pa.InvoiceYear 
    AND ACCOUNTING.INVOICE.InvoiceId = pa.InvoiceId)
    Paul Palubinski

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes indeed.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Oct 2008
    Location
    Denver, CO
    Posts
    44
    Quote Originally Posted by pootle flump View Post
    Yes indeed.
    Thank you, sir. Every time I think I'm getting pretty handy with SQL, I'm reminded that I know very little. This will definitely help me in the future.
    Paul Palubinski

Posting Permissions

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