Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.sqlserver.programming > #31380

Re: Row_Number() in UPDATE statement

Show all headers | View raw

On Wednesday, September 23, 2009 at 1:46:33 PM UTC+5:30, Peso wrote:
> 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
> "Marilyn" <Mar...@discussions.microsoft.com> wrote in message 
> news:F285471C-E490-431C...@microsoft.com...
> > 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.

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

Back to microsoft.public.sqlserver.programming | Previous | Next | Find similar

Thread

Re: Row_Number() in UPDATE statement amita Gupta <amitagupta105@gmail.com> - 2022-07-16 04:44 -0700

csiph-web