Update from select (subquery) using lag function-CodePudding

I am working on an update query in SSMS to calculate the days between two different rows with matching serial numbers. The goal is to partition by serial number and then look at the lagging record and calculate the days between the two records. As a select query I have the correct information being populated. The error comes when trying to use the select portion as a subquery to update the records into the table so that I can import those values into another system to build data visualizations. Any help is appreciated!

UPDATE qa_combined_data2
SET
    qa_combined_data2.days_since_last_return =
        (SELECT serial_number,
                receive_date,
                datediff(day, receive_date, LAG(receive_date, 1)
                                            OVER (PARTITION BY serial_number 
                                                      ORDER BY receive_date)) *-1 
                AS days_between_returns_update
             FROM qa_combined_data2)
    WHERE qa_combined_data2.serial_number = qa_combined_data2.serial_number
      AND qa_combined_data2.receive_date = qa_combined_data2.receive_date;

CodePudding user response：

Try something like this:

UPDATE Q1
SET days_since_last_return = Q2.days_between_returns_update
FROM qa_combined_data2 Q1
INNER JOIN 
        (SELECT serial_number,
                receive_date,
                datediff(day, receive_date, LAG(receive_date, 1)
                                            OVER (PARTITION BY serial_number 
                                                      ORDER BY receive_date)) *-1 
                AS days_between_returns_update
             FROM qa_combined_data2) Q2
    ON Q1.serial_number = Q2.serial_number
      AND Q1.receive_date = Q2.receive_date;