CODEWITHSUNDEEP
sql-serverdatabasesql-updatelead
D.j. Black
D.j. Black

Reputation: 1

How to use Lead in Update SQL command

This works with SELECT:

(LEAD(AltCode,2) OVER (ORDER BY AltCode) ) from [clroot].[Material]

but not with Update

USE [MEKON-EPE]

UPDATE [clroot].Material
SET String1 = (LEAD(AltCode, 2) OVER (ORDER BY AltCode))
FROM [clroot].[Material]

I get this error:

Msg 4108, Level 15, State 1, Line 6
Windowed functions can only appear in the SELECT or ORDER BY clauses.

I want to update a specific column with the value of the value of next 2 columns.

Upvotes: -1

Views: 268

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 82010

Just to expand on my comment

with cte as (
  Select *
        ,NewVal = LEAD(AltCode,2) OVER (ORDER BY AltCode)
 from [clroot].[Material
)
Update cte
 set String1 = NewVal

Upvotes: 1

Related Questions