Inserting values into new column based on a LEAD () function-CodePudding

I have a column called Sales and I created a column sales_next. I want to base each row's value in sales_next using a LEAD function. I got this so far, but I for some reason can't figure out how to update every row.

INSERT INTO superstore_sales$ (sales_next)  
VALUES
(
(SELECT TOP 1
    LEAD(sales, 1) OVER (
        ORDER BY sales
    ) AS sales_next
    FROM superstore_sales$
    WHERE sales_next IS NULL
    ))

I have tried removing the TOP 1 and I get the multiple values in subquery error, of course because I am unsure how to tell SQL to grab one value for each row using the LEAD function.

CodePudding user response：

A derived table (or a CTE) is updatable, if I understand what you're trying to do this should work:

update t set sales_next = sn
from (
  select sales_next, Lead(sales, 1) OVER (ORDER BY sales) AS sn
  from superstore_sales$
  where sales_next IS null
)t;

CodePudding user response：

I figured it out.Aaron was right, no need to create the column physically. I only need to do it query time. I figured out how both LEAD and LAG operate and the final code was simple:

SELECT "Order ID", "Customer Name", Sales,
LEAD(Sales) OVER(ORDER BY Sales) as next_sales
FROM superstore_sales$;

Thank you for your help:)