Error in Update Statement

Question

update
       wlc.StageFile
 set
       ProductCatalog =
                           case RecordGood
                                         when 0 then 'Bad Data else
                               case Flag1
                                        when 1 then 'Missing Data'
                                    else case Flag2
                                                 when 1 then 'DifferentData'
                                                                  else 'NoData'
                                           end
           end
 end
where
 RecordProcessed = 0
 and RecordGood IN (1, 2)

Hi,

I see any error near "=". But everything seems to be good. Can any one let me know were am doing wrong.

Thanks

Answer 1

Looks like missing single quote. Try the following (corrected):

UPDATE wlc.StageFile 
SET    ProductCatalog = CASE RecordGood 
                          WHEN 0 THEN 'Bad Data' 
                          ELSE CASE Flag1 
                                 WHEN 1 THEN 'Missing Data' 
                                 ELSE CASE Flag2 
                                        WHEN 1 THEN 'DifferentData' 
                                        ELSE 'NoData' 
                                      END 
                               END 
                        END 
WHERE  RecordProcessed = 0 
       AND RecordGood IN (1,2) 

---

Kalman Toth, SQL Server & BI Training, SSAS OLAP, SSIS, SSRS; http://www.SQLUSA.com

Answer 2

Hello Adurs,

Please have a look
, how to use Nested Case Statment

http://stackoverflow.com/questions/505747/best-way-to-do-nested-case-statement-logic-in-sql-server

If you can provide the table structure and some data, what you wana achieve, it would be easy to provide exact solution.

Thanks

Answer 3

I also noticed a missing single quote in your statement.

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog