1

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.

1
  • You need 2 copies of source data - one for to obtain the data and another for to update. Use CTE or subquery.
    – Akina
    Jul 24, 2019 at 11:53

2 Answers 2

3

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.

1

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;
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.