title: sql case when newValue else previousValue
body: <p>I'm trying to create a Update from Select where in a specific case (88)  the previous value should be used to update the column field</p>
  
  <pre><code>UPDATE myTable
  SET MyDateCol = CASE MYSelect.Indicator 
                  WHEN 88 THEN @previosValue 
                  ELSE MYSelect.NewValue
  FROM  myTable
  INNER JOIN    (    ...    ) AS MYSelect
  
  ON myTable.ID = MYSelect.ID
  </code></pre>
  
  <p>EXAMPLE:</p>
  
  <pre><code>MYSelect             | myTable
                       |
  Indicator NewValue   | MyDateCol 
     0         1       |    1
     1         2       |    2
     88        3       |    2 &lt;-
     3         4       |    4
     4         5       |    5
     5         6       |    6
     6         7       |    7
     88        8       |    7 &lt;-
  </code></pre>
  
  <p>Full Update</p>
  
  <pre><code>UPDATE [dbo].[BestellDetails]
     SET [Datum] = CASE MYTABLE.WochenTag WHEN 88  THEN lag(MyDate) over(order by MYTABLE.MyDate)  ELSE MyDate END
  FROM  [BestellDetails]
  INNER JOIN
  (
      SELECT INNERTabelle.BestellDetailId,INNERTabelle.WochenTag, DATEADD (dd,INNERTabelle.WochenTag, INNERTabelle.NewDatum) AS MyDate
      FROM
      (
          SELECT TOP 100 PERCENT BestellDetailId, 
                      dbo.FirstDateOfWeekISO8601(dbo.Bestellung.Jahr, 
                      dbo.Bestellung.Kalenderwoche) AS NewDatum,
                      (CASE BestellDetails.RefMenuId WHEN Speiseplandetails.RefMoId THEN 0 
                                                     WHEN Speiseplandetails.RefDiId THEN 1 
                                                     WHEN Speiseplandetails.RefMiId THEN 2 
                                                     WHEN Speiseplandetails.RefDoId THEN 3 
                                                     WHEN Speiseplandetails.RefFrId THEN 4 
                                                     WHEN Speiseplandetails.RefSaId THEN 5 
                                                     WHEN Speiseplandetails.RefSoId THEN 6 ELSE 88 END) AS WochenTag
          FROM            dbo.Speiseplandetails 
          RIGHT OUTER JOIN dbo.BestellDetails ON dbo.BestellDetails.RefMenuId = dbo.Speiseplandetails.RefMoId 
          OR dbo.BestellDetails.RefMenuId = dbo.Speiseplandetails.RefDiId 
          OR dbo.BestellDetails.RefMenuId = dbo.Speiseplandetails.RefMiId 
          OR dbo.BestellDetails.RefMenuId = dbo.Speiseplandetails.RefDoId 
          OR dbo.BestellDetails.RefMenuId = dbo.Speiseplandetails.RefFrId 
          OR dbo.BestellDetails.RefMenuId = dbo.Speiseplandetails.RefSaId 
          OR dbo.BestellDetails.RefMenuId = dbo.Speiseplandetails.RefSoId 
  
          RIGHT OUTER JOIN dbo.Bestellung ON dbo.BestellDetails.RefBestellId = dbo.Bestellung.BestellId
  
          order by BestellDetailId
  
      ) as INNERTabelle
  ) AS MYTABLE
  
  ON [BestellDetails].BestellDetailId = MYTABLE.BestellDetailId
  </code></pre>