-
Hi Friends,
I have 2 tables LoanMaster (Firstname,Lastname,LoanBal) and LoanPayment (Firstname,Lastname,Payment).
I want to update LoanMaster using the transaction table LoanPayment, deducting LoanBal with Payment, matching
corr Names of employee.
Is this code riqht?
update LoanMaster x set x.LoanBal=(x.LoanBal - y.Payment)
where x.Firstname = (select y.Firstname from y.LoanPayment
where y.Firstname=x.Firstname)
and x.Lastname=(select y.Lastname from y.LoanPayment
where y.Lastname=x.Firstname)
/
Kindly help please...
-
Hi,
What I feel personally is that your table relationship is not correct.It should be :
LoanMaster (
Customer_id PK,
Firstname,
Lastname,
LoanBal )
LoanPayment (
Customer_id,
Payment)
Query
UPDATE LoanMaster x
SET x.LoanBal=x.LoanBal-(SELECT y.Payment from LoanPayment y
WHERE x.Customer_id =y.Customer_id );
Hope it helps...
Take Care.
-
Thank you very much nishant,
-
Hi,
Do create the master-detail relationship and in detail table put some flag so that when master is upadted then flag in detail is set to false.Add y.flg='T' in the where clause.This will give the correct result.You should always first try to make your table structure correctly with all the constraints,then making any kind of query would be very simple.
Take Care.
-
Hi nishant,
What if I have two fields to be updated Loanbal1,Loanbal2
for Payment1,Payment2...would this be like this?...
UPDATE LoanMaster x
SET x.LoanBal1=x.LoanBal1-(SELECT y.Payment1 from LoanPayment y
WHERE x.Customer_id =y.Customer_id )
x.LoanBal2=x.LoanBal2-(SELECT y.Payment2 from LoanPayment y
WHERE x.Customer_id =y.Customer_id );
Thanks,
peter
-
Is it possible to place one where clause for
the two fields to be updated?
Thanks
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|