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.