Get and enter value from next record by Foreach on select result

Transact-SQL
1

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. Thank you

NONO
NONO507×526 6.16 KB

2

SELECT ID, IDxNumber, LEAD(IDxNumber,1,0) OVER (ORDER BY IDXNumber) AS HotCode
FROM myTable

Edit: Whoops, sorry about that, corrected above:

3

I'm missing here OVER. I need a complete code please

4

A little searching gets you this: LEAD (Transact-SQL) - SQL Server | Microsoft Docs

We cannot provide a complete OVER clause without seeing the full query and expected results. But just based on the screenshot, then OVER(ORDER BY ID) should work.

5

The OVER is:
OVER (order by IdxNumber)

Thanks. But I need to change the values in the table itself, not just display them

6

Thanks. But I need to change the values in the table itself, not just display them

Can you help me?

7 
;WITH CTE AS(
SELECT ID, IDxNumber, HotCode, LEAD(IDxNumber,1,0) OVER (ORDER BY IDXNumber) AS NewHotCode
FROM myTable)
UPDATE CTE SET HotCode=NewHotCode
1 Like