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:
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?