Click here to Skip to main content
14,486,801 members
Rate this:
Please Sign up or sign in to vote.
See more:
customer(CUST_ID,CUST_NAME,CUST_ADDRESS,EMP_ID)

table take(CUST_ID,LOAN_NUMBER)

table loan (LOAN_NUMBER,AMOUNT,BRANCH_NAME)


customers named Jones are not taking a loan anymore

i have tried a query but when i try removing from loan table its not working.

What I have tried:

delete
from take t,loan l
where exists (select c.cust_name from customer c where c.cust_id=t.cust_id and c.cust_name='Jones')
Posted
Updated 15-Mar-20 5:08am
v5
Rate this:
Please Sign up or sign in to vote.

Solution 2

Remember what I have answered earlier...
1. that you should use the subquery to validate that the correct items would be updated?
2. you need to do some more research on how EXIST works
These apply here as well...

Using this is just asking for trouble... IF for some reason this would work it will delete ALL entries in the Take and Loan tables IF there is a customer names 'Jones'.
WHERE Exists (
  SELECT c.cust_name
  FROM   customer c
  WHERE  c.cust_id = t.cust_id
  AND    c.cust_name='Jones'
)
What you actually want to do is to select all of the loan numbers for that one customer...

What I would do is to relocate that JOIN into the subquery to find all of the Loan Numbers
SELECT     t.Loan_Number
FROM       Take     t
INNER JOIN Customer c ON t.Cust_ID = c.CustID
WHERE      c.cust_name='Jones'
And then use this as a subquery to delete all of the loans first...
DELETE Loan
WHERE  Loan_Number IN (
  -- subquery 
)
This should have removed all of the Loan entries...

Now as a follow up, remove the entries from the Take table which was used to bridge the Customers to their Loans.
DELETE Take
WHERE Cust_ID IN (
  SELECT Cust_ID
  FROM   Customer
  WHERE  Cust_Name='Jones'
)
   
Comments
   
DELETE Loan
WHERE loan_number IN (SELECT t.loan_number
FROM take t JOIN customer c ON c.cust_ID=t.cust_ID
WHERE c.cust_name='Jones')

something like this ?
MadMyche 15-Mar-20 11:27am
   
Yes...
I didnt populate it so that you would (1) have a framework to use and (2) didnt want to type it again
   
its giving me an error :
ORA-02292: integrity constraint (SYS.FK_LOAN_TAKE) violated - child record found
MadMyche 15-Mar-20 11:57am
   
This is the sign of a Foreign Key constraint; it looks like there may be another reference to that Loan
   
Yes a last table called branch(branch_name,branch_city,assets)
MadMyche 15-Mar-20 12:15pm
   
Then you would need to delete those subordinated entries first.
Rate this:
Please Sign up or sign in to vote.

Solution 1

Why would you expect that to work?

It should logically either delete no records, or all of them, depending on whether your SELECT returns any rows or not - it doesn't tie the tables together in any meaningful way.
Start by looking at SELECTING rows instead of deleting them - it's quicker then restoring you DB after each test run - and look for a double JOIN that returns the right data from the take and loan tables associated with the right IDs from the customer table.

When that works, change it to a DELETE.
   
Comments
   
i tried a select query with join , still i cannot figure out how to convert it to delete

select c.cust_name,c.cust_id,l.loan_number
from customer c
join take t on c.cust_id=t.cust_id
join loan l on l.loan_number=t.loan_number
where c.cust_name = 'Smith'

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100