Row_Number() in UPDATE statement
Marilyn
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
(
SELECT
fieldToUpdate
,ROW_NUMBER().... as Seq
FROM dbo.Sometable
)
UPDATE upd
SET fieldToUpdate = Seq
"Marilyn" <Mar...@discussions.microsoft.com> wrote in message
news:F285471C-E490-431C...@microsoft.com...
Marilyn
Thank you very much for your help. This solves my problem.
Marilyn
Erland Sommarskog
> Is it possible to use Row_Number() function in an UPDATE statement?
Yes.
> 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..."
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.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
--CELKO--
Yes; it is on the same level as any other ANSI SQL Standard aggregate
function. If it fails, bitch to Microsoft :)
Peso
UPDATE f
SET LYRNO = rowno
FROM (
SELECT LYRNO,
ROW_NUMBER() OVER(PARTITION BY cokey ORDER BY chkey) AS rowno
FROM chorizon
) AS f
"Marilyn" <Mar...@discussions.microsoft.com> wrote in message
news:F285471C-E490-431C...@microsoft.com...
