SQL: Windowed functions can only appear in the SELECT or ORDER BY clauses
16-Nov-1212 Leave a comment
Use a CTE. Problem could occur in
WITH myCTE AS
(
SELECT WO, QuoteNo, EntryNo = ROW_NUMBER() OVER (PARTITION BY QuoteNo ORDER BY WO)
FROM dbo.Orders2006
WHERE QuoteNo IS NOT NULL
)
UPDATE dbo.Orders2006
SET EntryNo = myCTE.EntryNo
FROM dbo.Orders2006
INNER JOIN myCTE ON myCTE.WO = dbo.Orders2006.WO
With thanks to:
http://stackoverflow.com/questions/1466963/sql-row-number-function-in-where-clause