Update from select (subquery) using lag function

Question

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;

Answer 1

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;

Answer 2

Your primary issue is that you are selecting extra columns in the subquery. Also your WHERE makes no sense, and should probably be on the inside of the suubquery.

A further issue is that you don't need a subquery and re-querying the base table. You can use a derived table or CTE and just query the base table once.

Also:

• * -1 is the same as -.
• But you don't need that anyway, because you can swap the LAG parameters.
• LAG(..., 1) is the default.

UPDATE qad
SET
    days_since_last_return = days_between_returns_update
FROM (
    SELECT *,
        DATEDIFF(day, LAG(receive_date)
                       OVER (PARTITION BY serial_number 
                             ORDER BY receive_date)
                     , receive_date)
                AS days_between_returns_update
    FROM qa_combined_data2
) qad;