CODEWITHSUNDEEP
sqlsql-serverazure-synapse
ASH
ASH

Reputation: 20352

Update Based on Self-Join

I'm trying to do a simple calculation and update a field based on matching IDs and AsOfDates. Here is my script.

UPDATE A
SET A.Vol30Days = 
    (SELECT STDEV(PX_BID) OVER (ORDER BY ID, AsOfDate ROWS BETWEEN 30 PRECEDING AND CURRENT ROW)
    FROM Prices)
FROM Prices A
    INNER JOIN Prices B
    ON A.ID = B.ID
    AND A.AsOfDate = B.AsOfDate

The Subquery works fine and the logic looks ok, but SQL Server is throwing an error, saying FROM clause in UPDATE and DELETE statements cannot contain Subquery sources or joins.

Upvotes: 1

Views: 139

Answers (5)

Ron Dunn
Ron Dunn

Reputation: 3078

Here's the documentation on the recommended approach:

https://learn.microsoft.com/en-us/sql/t-sql/queries/update-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15#ah-ansi-join-replacement-for-update-statements

  • Create a transient table with the results of the JOIN clause
  • Write the UPDATE referencing the transient table

Please note that FROM clauses with JOINs - your original strategy - are currently in private preview and will soon move to public preview.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Try using a subquery. I think this is the equivalent logic:

UPDATE Prices
    SET Vol30Days = (SELECT STDEV(PX_BID)
                     FROM (SELECT TOP (31) p2.*
                           FROM Prices p2
                           WHERE p2.ID <= prices.ID
                          ) p2
                    );

Upvotes: 1

Marc Guillot
Marc Guillot

Reputation: 6465

Use an intermediate storage, like a table variable.

DECLARE @BIDS TABLE (ID integer, BIDS numeric(18,2))

INSERT INTO @BIDS (ID, BIDS)
       SELECT A.ID, (SELECT STDEV(PX_BID) OVER (ORDER BY ID, AsOfDate ROWS BETWEEN 30 PRECEDING AND CURRENT ROW) FROM Prices)
       FROM Prices A
            INNER JOIN Prices B ON A.ID = B.ID AND A.AsOfDate = B.AsOfDate

UPDATE A SET Vol30Days = B.BIDS
FROM Prices A
     INNER JOIN @BIDS B ON B.ID = A.ID

Alternatively you can use a temporal table :

SELECT A.ID, (SELECT STDEV(PX_BID) OVER (ORDER BY ID, AsOfDate ROWS BETWEEN 30 PRECEDING AND CURRENT ROW) FROM Prices) AS BIDS
INTO #BIDS 
FROM Prices A
     INNER JOIN Prices B ON A.ID = B.ID AND A.AsOfDate = B.AsOfDate

UPDATE A SET Vol30Days = B.BIDS
FROM Prices A
     INNER JOIN #BIDS B ON B.ID = A.ID

DROP TABLE #BIDS

Upvotes: 0

Max Zolotenko
Max Zolotenko

Reputation: 1132

UPDATE A
SET A.Vol30Days = B.Vol30Days
FROM Prices A
    INNER JOIN  (
                    select  t1.ID,
                            t1.AsOfDate,
                            STDEV(PX_BID) OVER (ORDER BY ID, AsOfDate ROWS BETWEEN 30 PRECEDING AND CURRENT ROW)    as  Vol30Days
                    from    Prices t1
                ) B
    ON A.ID = B.ID
    AND A.AsOfDate = B.AsOfDate

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522712

Try using an updatable CTE:

WITH cte AS (
    SELECT Vol30Days AS VolOrig,
        STDEV(PX_BID) OVER (ORDER BY ID, AsOfDate ROWS BETWEEN 30 PRECEDING AND CURRENT ROW) AS VolNew
    FROM Prices
)

UPDATE cte
SET VolOrig = VolNew;

But given that the new data is just a derived quantity from the same table, you might want to avoid the update, since it might need to be redone any time the table's data changes. Instead, consider creating a view.

Upvotes: 1

Related Questions