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

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

CodePudding user response：

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;

CodePudding user response：

Sorry if I don't understand correctly but something like this?

UPDATE MYTABLE SET something = table.something
FROM (
SELECT ID, Title ,IDxNumber,  
       LEAD(IdxNumber,1,0) Over(order by idxnumber) 
       AS HotCode FROM MYTABLE
) table
WHERE MYTABLE.id = table.id