Window function in Update statement

Question

Is there a work around to use window functions in update statement

You can populate sample/original table using the code

Create table #table(
key1 Char(5),
date1 Date,
date2 Date
);

Insert into #table 
Values ('abc','2019-07-22',NULL);
Insert into #table Values ('abc','2019-07-23',NULL);
Insert into #table Values ('def','2019-07-22',NULL);
Insert into #table Values ('ghi','2019-07-22',NULL);
Insert into #table Values ('ghi','2019-07-23',NULL);

I want to partition and Order by key1 - check for NULL in date2 - leave NULL intact if max(date1) over (partition by key1 order by key1) = date1 and change to max(date1) over (partition by key1 order by key1) if the above condition is not true. This is a data vault concept by the way.

Original:

Required:

I tried the below code:

UPDATE #table
SET date2 = CASE WHEN MAX(date1) OVER (PARTITION BY key1 ORDER BY key1) = date1 
  THEN NULL 
 ELSE MAX(date1) OVER (PARTITION BY key1 ORDER BY key1) END
WHERE date2 IS NULL ;

I am getting an error message:

Msg 4108, Level 15, State 1
Windowed functions can only appear in the SELECT or ORDER BY clauses.

Answer 1

The error message defines the problem: you can't use a window function directly in an assignment (SET). You can use a CTE to pull out the aggregate and then apply the update to the CTE (which pushes it back to the table).

;WITH t AS 
(
  SELECT key1, date1, date2, 
    date1max = MAX(date1) OVER (PARTITION BY key1 ORDER BY key1) 
  FROM #table
)
UPDATE t SET date2 = CASE date1max 
  WHEN date1 THEN NULL ELSE date1max END
WHERE date2 IS NULL;

If dates are not unique for any given key, you might need to do something to deal with ties. Please add sufficient sample data that would represent all edge cases and how you want to handle those.

Answer 2

Alternatively,

UPDATE t
  SET 
      t.date2 = t1.date1
FROM #table t
     OUTER APPLY
(
    SELECT MAX(date1) date1
    FROM #table t1
    WHERE t1.key1 = t.key1
          AND t1.date1 > t.date1
) t1;

OR

UPDATE t
  SET 
      t.date2 = t1.date1
FROM #table t
     OUTER APPLY
(
    SELECT TOP 1 date1
    FROM #table t1
    WHERE t1.key1 = t.key1
          AND t1.date1 > t.date1
    ORDER BY t1.date1 DESC
) t1;