Geek asks and answers

Row exception in SQL Server 2008 update

I am trying to update a specific set of rows with specific information. This means that I can’t execute the simple update statement, but instead I will need to specify the lines that I want to change and the values ​​by which I will correct them. In short, I want to update a column of dates that currently do not match the corresponding parameter; Thus, the following statement seems logical:

UPDATE contactparameter  
SET effectiveto = CASE parameterid    
When '2887' Then '13-Aug-2012'  
When '2896' Then '21-Feb-2012'   
When '3008' Then '28-Oct-2012'   
When '3272' Then '18-Jan-2013'   
END

If I then update, ALL rows that are not included in this list, i.e. all lines in which the parametric elements are NOT "2887", "2896", "3008" or "3272" are then freed. I tried to select the rows that I want to update in the following query:

UPDATE contactparameter   
SET effectiveto = CASE parameterid   
When '2887' Then '13-Aug-2012'   
When '2896' Then '21-Feb-2012'   
When '3008' Then '28-Oct-2012'   
When '3272' Then '18-Jan-2013'   
END   
WHERE exists
    (SELECT cp.parameterid   
        from contact c   
       INNER JOIN contactparameter cp on c.serialnumber = cp.serialnumber   
       WHERE cp.effectivefrom is not null  
        and cp.effectiveto is null)

. SQL, , - . ?

+3
2

:

UPDATE contactparameter   
SET effectiveto = CASE parameterid   
   When '2887' Then '13-Aug-2012'   
   When '2896' Then '21-Feb-2012'   
   When '3008' Then '28-Oct-2012'   
   When '3272' Then '18-Jan-2013'   
END   
from contact c   
INNER JOIN contactparameter cp on c.serialnumber = cp.serialnumber   
WHERE cp.effectivefrom is not null  
and cp.effectiveto is null
+1

,

# 1, ELSE CASE, , , NULL :

UPDATE contactparameter  
SET effectiveto = CASE parameterid    
When '2887' Then '13-Aug-2012'  
When '2896' Then '21-Feb-2012'   
When '3008' Then '28-Oct-2012'   
When '3272' Then '18-Jan-2013'   
Else effectiveto
END

, # 2, WHERE, , CASE:

UPDATE contactparameter  
SET effectiveto = CASE parameterid    
When '2887' Then '13-Aug-2012'  
When '2896' Then '21-Feb-2012'   
When '3008' Then '28-Oct-2012'   
When '3272' Then '18-Jan-2013'   
END
WHERE parameterid IN('2887','2896','3008','3272')

.

+1

More articles:


All Articles