Syntax error

Question

What is wrong with the expression below? I keep getting the following error message .... Server: Msg 156, Level 15, State 1, Line 4 Invalid syntax next to 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

Now your expression will be encoded (as soon as you add a column reference to the case statement, as mentioned in other posts), however, so that the rest of your syntax is uncommented, this will be a bad service for others in your situation.

, , Update 3 4 ( ).

select #Temp @Table, , .

@table:

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 )

, , , .

, , , . sql, , , .

Answer 2

, , CASE.

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

?

Answer 3

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

You can do this in two 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

It looks like your case statement should indicate which column is being tested for a 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 ) ) )