Deleting rows with no records in a join table

[lution]

Trying to clean up some data and I know the solution is simple but its eluding me, even after searching these forums.

I have 2 tables: tblFinancialTransactions and tblTransactionCase. The join field for the two tables is intPaymentID. I'm wanting to delete the records in tblFinancialTransactions that don't have at least one matching record in tblTransactionCase.

The last delete query I tried was:
DELETE tblFinancialTransactions.*, tblTransactionCase.intPaymentID
FROM tblFinancialTransactions LEFT JOIN tblTransactionCase ON tblFinancialTransactions.intPaymentID = tblTransactionCase.intPaymentID
WHERE (((tblTransactionCase.intPaymentID) Is Null));

I get the error: Could not delete from the specified tables

I've tried taking tblTransactionCase.intPayment out of the Delete clause but I get the same error.

Thanks in advance for the help.
-Lution

 

[MStef]

Make a DELETE in tblFinancialTransactions, only, and in the RELATIONSHIPS select "Enforce Referential Integrity", "Cascade Update Related Fields" and
"CASCADE DELETE RELATED RECORDS". In that case when you Delete a record in tblFinancialTransactions, the records in the tblTransactionCase will be deleted, (if they exists).
Look at attachment (word, zip).

 

[jal]

Maybe part of the problem is that your DELETE statement is trying to delete from two tables:

DELETE tblFinancialTransactions.*, tblTransactionCase.intPaymentID

 

[lution]

I ended up getting the delete using several steps:

1. Run a create table query that added a row for each entry in tblfinancialtransactions that didn't have a matching entry in tbltransactioncase. When I did this query, I had to modify the join link so that it picked up all the rows in tblFinancialTransactions.

SELECT tblFinancialTransactions.intPaymentID INTO utblBadTransactions
FROM tblFinancialTransactions LEFT JOIN tblTransactionCase ON tblFinancialTransactions.intPaymentID = tblTransactionCase.intPaymentID
WHERE (([tblTransactionCase].[intpaymentid] Is Null));



2. Then did a delete in tblfinancialtransactions where the payment id was in a select of the paymentid's from the temporary table:

delete from tblFinancialTransactions where intPaymentID in (select intPaymentID from utblBadTransactions);

The reason I'm having to do this was I had a bug in my code and I needed to get the data cleaned up so I could setup the relationship.

 

[Brianwarnock]

How can this possibbly be logical, or am I missing something?

DELETE tblFinancialTransactions.*, tblTransactionCase.intPaymentID
...
WHERE (((tblTransactionCase.intPaymentID) Is Null));

wouldn't it have been easier to create a new table tblFinancialTransactions where the tblTransactionCase.intPaymentID was not Null?

Brian

 

[lution]

It might have, but I'm trying to clean up the data that was added to the existing tables before I was able to add the referential integrity.

 

[LPurvis]

Upon summary glance through I don't think there's been a standard offering such as:

DELETE *
FROM tblFinancialTransactions
WHERE Not Exists (SELECT Null FROM tblTransactionCase WHERE tblFinancialTransactions.intPaymentID = tblTransactionCase.intPaymentID)


That could prove to be better performing than the often suggested

DELETE *
FROM tblFinancialTransactions
WHERE intPaymentID Not In (SELECT intPaymentID FROM tblTransactionCase)

(Which can seem counter intuitive with a non-corrolated subquery - but "Not In" can't be optimised).

Cheers.