Search code examples
sqllead

SQL - Get and update value from next record by Foreach on select result

I do not have much experience in SQL so my question is probably simple: On the result of a SELECT query, I need to UPDATE all records so that each record gets a value that is in the next record, as shown in the screenshot. I would love to have an easy and ready code.

Note: I have the attached code:

SELECT ID, Title ,IDxNumber,  LEAD(IdxNumber,1,0) Over(order by idxnumber) AS HotCode from MYTABLE

It works to display the data. But I want to update the data in the HotCode column in the table itself, not just display them.

Thanks

Solution

  • On SQL Server, we can use an updatable CTE. Assuming the HotCode column already exist in your table:

    WITH cte AS (
    SELECT *, LEAD(IdxNumber, 1, 0) OVER (ORDER BY idxnumber) AS HotCodeNew
    FROM MYTABLE
)

UPDATE cte
SET HotCode = HotCodeNew;