CODEWITHSUNDEEP
sql-server
Pran K
Pran K

Reputation: 19

How can I update different column values of the same table with multiple conditions?

I am working with SQL Server 2014. I am new to SQL Server development and working on client's requirement. I need help with one report...

This is my table LOANS:

  pid   c_name   ProductFlag  Duration   Discription
=====================================================
1.    Ash       p           Annual     Vehicle loan
2.    JOHN     GOLD          Half       Gold Loan
3.    Prit     Home          Annual     Home loan 
4.    Pari      P            Half       Vehicle loan
5.    Stacy    Home          Annual     Home loan 
6.    Meet     Gold          Annual     Gold Loan
7.    Mandy    Gold          Quarterly   Gold Loan
8.    Rose     Home          Annual      Home loan  
9.    Rita    Tractor         Half       Tractor loan  
10.    Lary   Education       Half        Education loan

I want to update column Description based on Productflag values as 

update LOANS 
set description = CASE  
                     WHEN productflag = 'p' THEN 'applied for vehicle loan '
                     WHEN productflag = 'gold' THEN 'applied for gold loan'

and at same time I want to update column description based on Duration values.

No matter what the Productflag is, for that particular duration ....(except productflags 'p' and 'gold' which I want to update as mentioned above )

I just want to update column Description based on Duration values as follows:

update LOANS 
set description = CASE  
                     WHEN Duration = 'half' THEN 'applied for half year'
                     WHEN Duration = 'Quarter' THEN 'applied for Quarter year loan'

Following should be the output

 pid   c_name   ProductFlag  Duration   Discription
=====================================================
1.    Ash       p           Annual     applied for vehicle loan
2.    JOHN     GOLD          Half      applied for half year
3.    Prit     Home          Annual     Home loan 
4.    Pari      P            Half       applied for half year
5.    Stacy    Home          Annual    Home loan 
6.    Meet     Gold          Annual     applied for gold loan
7.    Mandy    Gold          Quarterly  applied for Quarter year loan
8.    Rose     Home          Annual      Home loan  
9.    Rita    Tractor         Half      applied for half year
10.    Lary   Education       Half       applied for half year

Please help as soon as possible, thanks!

Upvotes: 0

Views: 252

Answers (2)

KoP
KoP

Reputation: 86

From the question I understand, above answer of PrDK is correct (https://stackoverflow.com/a/40203237/4689372).

Seen your output, bit weird one I should say :). Please test with below code.

UPDATE LOANS 
SET DESCRIPTION = 
CASE 
    WHEN Duration = 'Quarterly' AND ProductFlag IN ('P','GOLD') THEN 'applied for Quarter year loan'
    WHEN Duration = 'Annual' AND ProductFlag IN ('GOLD') THEN 'applied for ANNUAL loan'
    WHEN Duration = 'Half' THEN 'applied for half year'
    WHEN ProductFlag NOT IN ('P','GOLD') THEN 'APPLIED FOR ' + ProductFlag + 'LOAN'
END

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93704

Add the duration condition after the productflag condition.

CASE statement evaluates input_expression = when_expression for each WHEN clause in the Order specified. 

UPDATE loans 
SET    description = 
       CASE 
              WHEN productflag = 'p' THEN 'applied for vehical loan ' 
              WHEN productflag = 'gold' THEN 'applied for gold loan' 
              WHEN duration = 'half' THEN 'applied for half year' 
              WHEN duration = 'Quarter' THEN 'applied for Quarter year loan'
             ...

Upvotes: 2

Related Questions