Updating Column value dynamicaly please help?

[nmmure]

Hi,

Please help me how to update column value based on the condition.

Here my table is having 3 columns

Customer_Code Loan_ID Account_Key
------------------- ----------- -----------------
CAM BAA 1LW
COM BOA 1LF
CMM BMA 1LV
CTM BTA 1LX

My Requirement was to Changing the Account_Key value based on the Customer_Code and Loan_ID.

For that I am using UPDATE and CASE statement and hot coded value it is working fine.
Please guide me instead of “Hot Coded Value” how to add the column value.

Here I am pasting my Update statement

Update Account (Table Name) set Account_Key =
Case
When Customer_Code= ' CAM ' and Loan_ID = ' BAA ' then ‘CAM-BAA-1LW'
When Customer_Code= ' COM ' and Loan_ID = ' BOA ' then ‘COM-BOA-1LF'
When Customer_Code= ' CMM ' and Loan_ID = ' BMA ' then ‘CMM-BMA-1LV'
When Customer_Code= ' CTM ' and Loan_ID = ' BTA ' then ‘CTM-BTA-1LX'
End

My table is having more than 1000 different customer_code values.

Please help me how to use Column values instead of Hot Coded values.

Thanks in advanuce

Thanks & Regards
Mure

 

[AlexCuse]

I think all you need is this:

update Account
set Account_Key = Customer_Code + '-' + Loan_ID + '-' + Account_Key

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[JPMontreal]

Try:

Update Account (Table Name) set Account_Key = Rtrim(Customer_Code) + ‘-‘ + rtrim( Loan_ID) + ‘-‘ + rtrim(Account_Key)


Jean-Paul
Montreal
To send me E-Mail, remove “USELESSCODE”.
jp@USELESSCODEsolutionsvba.com

 

[AlexCuse]

Ah yes, rtrim is a good idea, in case your fields are CHAR rather than VARCHAR.

However, isn't the back-tick used in MySQL?

I think you would want this:

update Account
set Account_Key = rtrim(Customer_Code) + '-' + rtrim(Loan_ID) + '-' + rtrim(Account_Key)

You shouldn't use the rtrim function if you don't have to, so check the column's data type first.

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[nmmure]

Thanks a lot

It's working fine

-Mure