CODEWITHSUNDEEP
sql-server-2008
Bharath Singh H
Bharath Singh H

Reputation: 13

Update with multiple CASE

BEGIN TRAN    
UPDATE SubUnitAreas  
        SET SA.GIA = CASE TEMP.[Rentable SF] WHEN (TEMP.[Useable SF] <= 0  AND TEMP.[Rentable SF] <= 0) THEN  TEMP.[Rentable SF] ELSE END,   
        SA.GIAMeas = 1,   
        SA.NIA = CASE TEMP.[Useable SF] WHEN (TEMP.[Useable SF] <= 0  AND TEMP.[Rentable SF] <= 0) THEN  TEMP.[Useable SF] ELSE END,   
        SA.NIAMeas = 1  
    FROM SubUnitAreas SA  
        INNER JOIN SubUnit SU ON SA.SubUnitKey = SU.SubUnitKey  
        INNER JOIN TRT_140317_SubUnitAreas TEMP ON TEMP.[LEASE ID] = SU.TenureID  
    WHERE SA.SubUnitKey = SU.SubUnitKey AND SU.TenureID NOT IN ('DUB008')  
ROLLBACK TRAN

Upvotes: 0

Views: 34

Answers (2)

Bharath Singh H
Bharath Singh H

Reputation: 13

I just had to give an ELSE <>

BEGIN TRAN UPDATE SA SET SA.GIA = CASE WHEN (TEMP.[Useable SF] <= 0 AND TEMP.[Rentable SF] <= 0) THEN TEMP.[Rentable SF] ELSE SA.GIA END, SA.GIAMeas = 1, SA.NIA = CASE WHEN (TEMP.[Useable SF] <= 0 AND TEMP.[Rentable SF] <= 0) THEN TEMP.[Useable SF] ELSE SA.NIA END, SA.NIAMeas = 1 FROM SubUnitAreas SA INNER JOIN SubUnit SU ON SA.SubUnitKey = SU.SubUnitKey INNER JOIN TRT_140317_SubUnitAreas TEMP ON TEMP.[LEASE ID] = SU.TenureID WHERE SA.SubUnitKey = SU.SubUnitKey AND SU.TenureID NOT IN ('DUB008', 'MT001') ROLLBACK TRAN

Upvotes: 0

Dan
Dan

Reputation: 10700

Certainly, but you have a syntax error in your code, as you did not supply a value in the ELSE-case. Furthermore, if you want to use a logical expression after every WHEN-keyword, you should not supply a value after the CASE-keyword. If you want to leave a column to its original value, in case none of the conditions are satisfied, just do this:

UPDATE SubUnitAreas SET 
    SA.GIA = CASE WHEN <<logical expression>> THEN TEMP.[Rentable SF] ELSE SA.GIA END,
    SA.GIAMeas = 1,
    ...

Upvotes: 1

Related Questions