EN VI

SQL UPDATE with COALESCE and PARTITION BY statements?

How to SQL UPDATE with COALESCE and PARTITION BY statements

I am trying to update NULL column values with values from the same column that are from the same Item and DateAdded:

Before:

DateAdded	Item	SalePrice	SaleDate
1/02/2024	Apple	99	1/12/2024
1/02/2024	Apple	NULL	NULL
2/05/2024	Apple	102	2/12/2024
2/05/2024	Apple	NULL	NULL
2/05/2024	Banana	NULL	NULL
2/05/2024	Banana	101	2/13/2024
2/05/2024	Banana	NULL	NULL
2/06/2024	Banana	NULL	NULL

After:

DateAdded	Item	SalePrice	SaleDate
1/02/2024	Apple	99	1/12/2024
1/02/2024	Apple	99	1/12/2024
2/05/2024	Apple	102	2/12/2024
2/05/2024	Apple	102	2/12/2024
2/05/2024	Banana	101	2/13/2024
2/05/2024	Banana	101	2/13/2024
2/05/2024	Banana	101	2/13/2024
2/06/2024	Banana	NULL	NULL

I get the following error with the below code:

Update table1
SET SaleDate  = ISNULL(SaleDate, coalesce(SaleDate,  max(SaleDate)  over (partition by Item, CAST([DateAdded] AS DATE)))
   ,SalePrice = ISNULL(SalePrice,coalesce(SalePrice, max(SalePrice) over (partition by Item, CAST([DateAdded] AS DATE)))
WHERE CAST(DateAdded  AS DATE) > '2024-01-01'

Windowed functions can only appear in the SELECT or ORDER BY clauses.

Solution:

As clearly shown in the error message, you can't use window functions directly in an UPDATE, you need a subquery.

But you can update the subquery directly, no need to join.

WITH cte AS (
    SELECT *,
      NewSaleDate  = ISNULL(SaleDate,  max(SaleDate)  over (partition by Item, CAST(DateAdded AS DATE)),
      NewSalePrice = ISNULL(SalePrice, max(SalePrice) over (partition by Item, CAST(DateAdded AS DATE))
    FROM YourTable t
    WHERE DateAdded >= CAST('20240102' AS datetime)
)
UPDATE cte
SET SaleDate  = NewSaleDate,
    SalePrice = NewSalePrice
WHERE (SaleDate IS NULL OR SalePrice IS NULL);

In SQL Server 2022+ and Azure, it may be better to use DATETRUNC(day, DateAdded) rather than casting.