T-SQL question criteria 1

[djj55]

Hello, I have an update that the criteria I believe needs help. However, I am having trouble thinking about it (bad day).

UPDATE A 
SET MyColumn = 
    CASE 
        WHEN LEFT(C.MyColumn2, 4) = 'VAL1' THEN 'Val1A' 
        WHEN C.MyColumn2 = 'VAL2' THEN 'Val2A' 
        WHEN C.MyColumn2 = 'VAL3' THEN 'Val3A' 
        WHEN C.MyColumn2 = 'VAL4' THEN 'Val4A' 
        WHEN C.MyColumn2 = 'VAL5' THEN 'Val5A'
    END 
FROM MyTable1 A 
INNER JOIN MyTable2 C 
ON A.MyColumn3 = C.MyColumn3 
WHERE MyColumn <>
    CASE 
        WHEN LEFT(C.MyColumn2, 4) = 'VAL1' THEN 'Val1A' 
        WHEN C.MyColumn2 = 'VAL2' THEN 'Val2A' 
        WHEN C.MyColumn2 = 'VAL3' THEN 'Val3A' 
        WHEN C.MyColumn2 = 'VAL4' THEN 'Val4A' 
        WHEN C.MyColumn2 = 'VAL5' THEN 'Val5A'
    END 
    OR MyColumn IS NULL

The CASE statement is the problem.

Thanks for pointers / comments.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[markros]

Try

;with cte as (select A.PK, A.myColumn, F.myNewColumn =
    
from myTable1 inner join myTable2 C on A.MyColumn3=c.myColumn3
cross apply (select CASE 
        WHEN LEFT(C.MyColumn2, 4) = 'VAL1' THEN 'Val1A' 
        WHEN C.MyColumn2 = 'VAL2' THEN 'Val2A' 
        WHEN C.MyColumn2 = 'VAL3' THEN 'Val3A' 
        WHEN C.MyColumn2 = 'VAL4' THEN 'Val4A' 
        WHEN C.MyColumn2 = 'VAL5' THEN 'Val5A' as myNewColumn) F
where A.myColumn IS NULL or A.myColumn <> F.myNewColumn)

update cte set myColumn = myNewColumn

PluralSight Learning Library

 

[djj55]

Sorry it was such a long time to get back.

Still learning cte but this works well.

Thanks,

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!