SQLTeam.com | Weblogs | Forums

Regarding Update

#1

Hi All,

I Have a Senarion, I have two tables on is tbl_CreditLine_Funding_Details and tbl_FundingLine_Entry,
I vl pass the Creditline_id based on this it should take the FUNDER_NAME_Id and FUNDING_BRANCH_ID
and update the loan_assigned with Amount_Sanctioned for that credit line. please find the table details with sample data:
[tbl_FundingLine_Entry]
ID Funding_Type_Id Funder_Name_Id Funding_Branch_Id Net_Balance User_Id Modifiedon Loan_Assigned
1 1061 1068 NULL 1542145.00 1 2015-10-21 10:59:26.113 0.00
3 1067 1070 NULL 232332.00 1 2015-10-21 05:38:34.000 0.00
5 1073 1075 1076 252500.00 1 2015-12-03 16:24:49.000 94000.00

tbl_CreditLine_Funding_Details:
ID CreditLine_Id Funding_Instituition_Id Funding_Name_Id Funding_Branch_Id Amount_Sanctioned Comments
7 21 1061 1068 NULL 25000.00
8 21 1067 1070 NULL 25000.00
9 22 1073 1075 1076 25000.00
10 22 1067 1070 NULL 25000.00
11 23 1067 1070 NULL 28000.00
12 23 1073 1075 1076 22000.00

kindly help me, its very urgent.

Thanks & Regards,
Kiran Murali

#2

So, how are you trying to update the [Loan_Assigned] in the [tbl_FundingLine_Entry] table from the [Amount_Sanctioned] column in the t[CreditLine_Funding_Detail] table

Application, procedure, trigger, update statement...

#3

using, stored procedure..parameter is Creditline_id

#5

The following will work (although I haven't tested it because you didn't post your data in a readily consumable format) BUT ONLY IF THE COMBINATION OF CreditLine_ID, Funder_Name_ID, and Funding_Branch_ID are guaranteed to form a unique set of rows in the tbl_CreditLine_Funding_Details table. You've also spelled Funder_Name_ID and Funding_Name_ID differently in the two tables. Since I'm not sure which is right, I made them both the same and you'll need to check and make corrections as necessary.

 UPDATE fl
    SET fl.Loan_Assigned = cl.Amount_Sanctioned
   FROM tbl_FundingLine_Entry fl
   JOIN tbl_CreditLine_Funding_Details
     ON fl.Funder_Name_ID = cl.Funder_Name_ID
    AND fl.Funding_Branch_ID = cl.Funding_Branch_ID
  WHERE cl.CreditLine_ID = @pCreditline_id

;

#6

Thx..its working..

#7

Good. Thanks for the feedback. The question now is, do you understand how and why it works?

#8

Yes.. I understood the query..