Syntax Error

Question

What is wrong with the statement below? I keep getting the following error message.... Server: Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'THEN'.

update oildatasetstatus
set oildatasetstatusid = 
    case 
    WHEN 5 THEN 16        
    WHEN 6 THEN 17        
    WHEN 7 THEN 18        
    WHEN 8 THEN 18        
    WHEN 9 THEN 18        
    WHEN 10 THEN 19        
    WHEN 11 THEN 20    
    End
where oildatasetlabstatusid in 
(
                select oildatasetstatusid
                from OilDataSetStatus
                inner join OilDataSet on OilDataSet.OilDataSetID = 
                    OilDataSetStatus.OilDataSetID
                where SamplePointID in 
                (
                                select SamplePointID 
                                from SamplePoint
                                where CustomerSiteID in
                                (
                                                select CustomerSiteID
                                                from CustomerSite
                                                where CustomerID = 2
                                )
                )
)

Answer 1

Looks like your case statement needs to specify which column is being tested for the given value.

For example:

 update  oildatasetstatus
    set     oildatasetstatusid = case WHEN oildatasetstatusid = 5 THEN 16
                                      WHEN oildatasetstatusid = 6 THEN 17
                                      WHEN oildatasetstatusid = 7 THEN 18
                                      WHEN oildatasetstatusid = 8 THEN 18
                                      WHEN oildatasetstatusid = 9 THEN 18
                                      WHEN oildatasetstatusid = 10 THEN 19
                                      WHEN oildatasetstatusid = 11 THEN 20
                                 End
    where   oildatasetlabstatusid in (
            select  oildatasetstatusid
            from    OilDataSetStatus
                    inner join OilDataSet on OilDataSet.OilDataSetID = OilDataSetStatus.OilDataSetID
            where   SamplePointID in (
                    select  SamplePointID
                    from    SamplePoint
                    where   CustomerSiteID in ( select  CustomerSiteID
                                                from    CustomerSite
                                                where   CustomerID = 2 ) ) )

Answer 2

I think you're missing the statement that you want to evaluate in the CASE statement.

update oildatasetstatus set oildatasetstatusid =
case oildatasetstatusid
 WHEN 5 THEN 16
 WHEN 6 THEN 17
 WHEN 7 THEN 18
 WHEN 8 THEN 18
 WHEN 9 THEN 18
 WHEN 10 THEN 19
 WHEN 11 THEN 20
End
where oildatasetlabstatusid in ( select oildatasetstatusid from OilDataSetStatus inner join OilDataSet on OilDataSet.OilDataSetID = OilDataSetStatus.OilDataSetID where SamplePointID in ( select SamplePointID from SamplePoint where CustomerSiteID in ( select CustomerSiteID from CustomerSite where CustomerID = 2 ) ) )

Give that a shot?

Answer 3

Your case statement does not have an object to work on.

You can do it 2 ways:

set oildatasetstatusid = 
    case oildatasetstatusid
    WHEN 5 THEN 16        
    WHEN 6 THEN 17        
    WHEN 7 THEN 18        
    WHEN 8 THEN 18        
    WHEN 9 THEN 18        
    WHEN 10 THEN 19        
    WHEN 11 THEN 20    
    End

or

set oildatasetstatusid = 
    case 
    WHEN oildatasetstatusid = 5 THEN 16        
    WHEN oildatasetstatusid = 6 THEN 17        
    WHEN oildatasetstatusid = 7 THEN 18        
    WHEN oildatasetstatusid = 8 THEN 18        
    WHEN oildatasetstatusid = 9 THEN 18        
    WHEN oildatasetstatusid = 10 THEN 19        
    WHEN oildatasetstatusid = 11 THEN 20    
    End

Answer 4

The way you have your statement coded now will work (once you add the column reference to the case statement, as mentioned by other posts), however, to let the rest of your syntax go uncommented on would be a disservice to others in your situation.

While you may only need to run this query once, I and others have run into similar situations where an Update to many rows also relies data 3 or 4 tables away from our source and has to be run many times (like in a report).

By collapsing your sub selects into a single select statement and saving the results of that into a #Temp table or a @Table variable, you only have to do that lookup once, then select from the result set for your update.

Here is a sample using a @table variable:

declare @OilStatus table (oilDatasetStatusID int)
insert into @OilStatus
    select odss.oildatasetstatusid
    from OildataSetStatus odss
    join oilDataSet ods on ods.OilDataSetID = odss.OilDataSetID
    join SamplePoint sp on sp.SamplePointID = odss.SamplePointID
    join CustomerSite cs on cs.CustomerSiteID = sp.CustomerSiteID
    where cs.CustomerID = 2

update oildatasetstatus
set oildatasetstatusid = 
 case oildatasetstatusid
  WHEN 5 THEN 16        
  WHEN 6 THEN 17        
  WHEN 7 THEN 18        
  WHEN 8 THEN 18        
  WHEN 9 THEN 18        
  WHEN 10 THEN 19        
  WHEN 11 THEN 20    
end
where oildatasetlabstatusid in ( select oilDatasetStatusID from @OilStatus )

Since I do not have your exact schema, there may be errors when trying to implement the sample above but I think you will get the idea.

Also, whenever multiple tables are used in a single statement try to preface every column name with an alias or the full table name. It helps keep both the sql engine and the people reading your code from getting lost.