0

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

Yashir
  • 3
  • 2
  • I suggest _not_ doing this update, as the hot code column depends on the data in the rest of the table, and is therefore _derived_ data. Just run this query whenever you need the hot code. – Tim Biegeleisen May 03 '22 at 08:50
  • You may be right in general, but I want to execute the query at least in parts of the table where I know HotCode is not used at all (this is a field that is almost never used). So I would love an answer anyway. Thanks. – Yashir May 03 '22 at 08:54
  • What is your _version_ of SQL (e.g. MySQL, SQL Server, Oracle, Postgres, etc.) ? Any answer would probably depend on this. – Tim Biegeleisen May 03 '22 at 08:55
  • This is SQL Server – Yashir May 03 '22 at 08:58

2 Answers2

0

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;
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
0

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
Konstantinos K.
  • 181
  • 1
  • 9