Fresh Taco
asked on
Needing some help removing SQL records within a query [SQLite]
So I am trying to write a query to remove the records where the Customer has only one OverdueFee. (I have a Loan Table and a Customer Table which both contain the same column CustomerID which in the Customer table is seen as a Primary Key AUTOINCREMENT) So far my query has DELETE FROM Loan WHERE EXISTS (SELECT * FROM Customer WHERE Customer.CustomerID = Loan.CustomerID Loan and Customer are two separate tables. Loan contains LoanID, CustomerID, InventoryItemID etc.... and the Customer Table contains CustomerID as well as others that dont really matter in this case. The OverdueFee is a column within the Loan Table with the REAL data type
I am wanting to remove all records when the Customer has only ONE overduefee in the Loan table so im guessing I have to somehow JOIN both the two tables by the CustomerID? Tad stuck so any help would be appreciated
I am wanting to remove all records when the Customer has only ONE overduefee in the Loan table so im guessing I have to somehow JOIN both the two tables by the CustomerID? Tad stuck so any help would be appreciated
ASKER
Hey so tried that but didnt seem to work, the SQLite doesnt seem to like that JOIN clause with the DELETE, im guessing I need to add a subquery somehow?
Ok, Pls try this -
DELETE K
FROM Loan K
WHERE k.OverdueFee IS NOT NULL AND k.OverdueFee > 0
AND EXISTS ( SELECT NULL FROM Customer C WHERE C.CustomerID = K.CustomerID )
DELETE K
FROM Loan K
WHERE k.OverdueFee IS NOT NULL AND k.OverdueFee > 0
AND EXISTS ( SELECT NULL FROM Customer C WHERE C.CustomerID = K.CustomerID )
ASKER
So that was able to successfully execute the query but what I believe it has done is delete all the records with OverdueFee greater than 0 which all of them will have because their fee is more than that. What I am trying to accomplish is removing all the records of the CustomerID's that appear in that loan table only once if that makes sense?.
can you provide some to validate the query ?
Per your request, you need to your subquery against the LOAN table itself because you need to check for more records.
In other words, see if this gives you the correct records:
If this returns the correct set of records, just change it to a DELETE:
SELECT LoanID, CustomerID, InventoryItemID, OverdueFee
FROM Loan
WHERE NOT EXISTS (
SELECT 1 FROM Loan x
WHERE x.CustomerID = Loan.CustomerID
AND x.LoanID <> Loan.LoanID
)If this returns the correct set of records, just change it to a DELETE:
DELETE
FROM Loan
WHERE NOT EXISTS (
SELECT 1 FROM Loan x
WHERE x.CustomerID = Loan.CustomerID
AND x.LoanID <> Loan.LoanID
)This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Take backup of the loan table and then try -
DELETE K
FROM Loan K
INNER JOIN Customer C WHERE C.CustomerID = K.CustomerID
WHERE k.OverdueFee IS NOT NULL AND k.OverdueFee > 0