Syntax Error

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
                                )
                )
)

From stackoverflow

• 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 ) ) )
  

• 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?

• 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
  

• 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.