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.
Best Answer
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).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.