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:

enter image description here

Required:

enter image description here

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.

• Akina: You need 2 copies of source data - one for to obtain the data and another for to update. Use CTE or subquery.

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;