Row_Number() in UPDATE statement

[Marilyn]

Hi,

Is it possible to use Row_Number() function in an UPDATE statement?

The following code works fine when I use a SELECT statement:

SELECT cokey, chkey, NLAYERS, ROW_NUMBER() OVER(PARTITION BY cokey ORDER BY
chkey) AS 'LYRNO' FROM chorizon

But what I need is to update the LYRNO column to store the row numbers
generated by the SELECT statement. So I have the following client-side T-SQL
code:

UPDATE chorizon SET LYRNO = (SELECT ROW_NUMBER() OVER (PARTITION BY cokey
ORDER by chkey) AS 'LYRNO' FROM chorizon)

However, I'm getting this error: "Subquery returned more than 1 value.
This is not permitted..."

Any assistance is greatly appreciated.

[Farmer]

WITH upd AS
(
SELECT
fieldToUpdate
,ROW_NUMBER().... as Seq
FROM dbo.Sometable
)
UPDATE upd
SET fieldToUpdate = Seq

Post by Marilyn
Hi,
Is it possible to use Row_Number() function in an UPDATE statement?
SELECT cokey, chkey, NLAYERS, ROW_NUMBER() OVER(PARTITION BY cokey ORDER BY
chkey) AS 'LYRNO' FROM chorizon
But what I need is to update the LYRNO column to store the row numbers
generated by the SELECT statement. So I have the following client-side T-SQL
UPDATE chorizon SET LYRNO = (SELECT ROW_NUMBER() OVER (PARTITION BY cokey
ORDER by chkey) AS 'LYRNO' FROM chorizon)
However, I'm getting this error: "Subquery returned more than 1 value.
This is not permitted..."
Any assistance is greatly appreciated.

[Marilyn]

Hi,

Thank you very much for your help. This solves my problem.

Marilyn

Post by Farmer
WITH upd AS
(
SELECT
fieldToUpdate
,ROW_NUMBER().... as Seq
FROM dbo.Sometable
)
UPDATE upd
SET fieldToUpdate = Seq

Post by Marilyn
Hi,
Is it possible to use Row_Number() function in an UPDATE statement?
SELECT cokey, chkey, NLAYERS, ROW_NUMBER() OVER(PARTITION BY cokey ORDER BY
chkey) AS 'LYRNO' FROM chorizon
But what I need is to update the LYRNO column to store the row numbers
generated by the SELECT statement. So I have the following client-side T-SQL
UPDATE chorizon SET LYRNO = (SELECT ROW_NUMBER() OVER (PARTITION BY cokey
ORDER by chkey) AS 'LYRNO' FROM chorizon)
However, I'm getting this error: "Subquery returned more than 1 value.
This is not permitted..."
Any assistance is greatly appreciated.

[Erland Sommarskog]

Post by Marilyn
Is it possible to use Row_Number() function in an UPDATE statement?

Yes.

Post by Marilyn
SELECT cokey, chkey, NLAYERS, ROW_NUMBER() OVER(PARTITION BY cokey ORDER
BY chkey) AS 'LYRNO' FROM chorizon
But what I need is to update the LYRNO column to store the row numbers
generated by the SELECT statement. So I have the following client-side
UPDATE chorizon SET LYRNO = (SELECT ROW_NUMBER() OVER (PARTITION BY cokey
ORDER by chkey) AS 'LYRNO' FROM chorizon)
However, I'm getting this error: "Subquery returned more than 1 value.
This is not permitted..."

Of course. You cannot cram the whole result set into each and every single
cell.

To this elegantly, you need use a CTE, a Common Table Expression.

WITH numbers AS (
SELECT LYRNO,
ROW_NUMBER() OVER(PARTITION BY cokey ORDER BY chkey) AS rowno
FROM chorizon
)
-- And here comes the fun part!
UPDATE numbers
SET LYRNO = rowno

If this looks bewildering to you, don't worry. I'm still not sure that
I have understood it myself... But essentially you are creating a
temporary view, and as long it's updateable, it works nice. A typical
situation when you need to use a CTE for updating is exactly when
you want number the rows in a table.

[--CELKO--]

Is it possible to use ROW_NUMBER() [aggregate] function in an UPDATE statement?<<

Yes; it is on the same level as any other ANSI SQL Standard aggregate
function. If it fails, bitch to Microsoft :)

[Peso]

You can also do the update a a derived table

UPDATE f
SET LYRNO = rowno
FROM (
SELECT LYRNO,
ROW_NUMBER() OVER(PARTITION BY cokey ORDER BY chkey) AS rowno
FROM chorizon
) AS f

Post by Marilyn
Hi,
Is it possible to use Row_Number() function in an UPDATE statement?
SELECT cokey, chkey, NLAYERS, ROW_NUMBER() OVER(PARTITION BY cokey ORDER BY
chkey) AS 'LYRNO' FROM chorizon
But what I need is to update the LYRNO column to store the row numbers
generated by the SELECT statement. So I have the following client-side T-SQL
UPDATE chorizon SET LYRNO = (SELECT ROW_NUMBER() OVER (PARTITION BY cokey
ORDER by chkey) AS 'LYRNO' FROM chorizon)
However, I'm getting this error: "Subquery returned more than 1 value.
This is not permitted..."
Any assistance is greatly appreciated.

[amita Gupta]

Post by Peso
You can also do the update a a derived table
UPDATE f
SET LYRNO = rowno
FROM (
SELECT LYRNO,
ROW_NUMBER() OVER(PARTITION BY cokey ORDER BY chkey) AS rowno
FROM chorizon
) AS f

Post by Marilyn
Hi,
Is it possible to use Row_Number() function in an UPDATE statement?
SELECT cokey, chkey, NLAYERS, ROW_NUMBER() OVER(PARTITION BY cokey ORDER BY
chkey) AS 'LYRNO' FROM chorizon
But what I need is to update the LYRNO column to store the row numbers
generated by the SELECT statement. So I have the following client-side
T-SQL
UPDATE chorizon SET LYRNO = (SELECT ROW_NUMBER() OVER (PARTITION BY cokey
ORDER by chkey) AS 'LYRNO' FROM chorizon)
However, I'm getting this error: "Subquery returned more than 1 value.
This is not permitted..."
Any assistance is greatly appreciated.

https://groups.google.com/g/microsoft.public.sqlserver.programming/c/41qlOqSOBYk?pli=1