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

Re: Row_Number() in UPDATE statement

X-Received	by 2002:a05:6214:23cb:b0:472:f1a5:5cea with SMTP id hr11-20020a05621423cb00b00472f1a55ceamr14609350qvb.13.1657971846184; Sat, 16 Jul 2022 04:44:06 -0700 (PDT)
X-Received	by 2002:a05:6871:729:b0:10c:6d6f:4bc8 with SMTP id f41-20020a056871072900b0010c6d6f4bc8mr9497136oap.288.1657971845899; Sat, 16 Jul 2022 04:44:05 -0700 (PDT)
Path	csiph.com!weretis.net!feeder6.news.weretis.net!news.misty.com!border2.nntp.dca1.giganews.com!nntp.giganews.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups	microsoft.public.sqlserver.programming
Date	Sat, 16 Jul 2022 04:44:05 -0700 (PDT)
In-Reply-To	<u39fqYCPKHA.1372@TK2MSFTNGP02.phx.gbl>
Injection-Info	google-groups.googlegroups.com; posting-host=223.178.213.71; posting-account=ewX8bwoAAAA2K5PtgTWhLZNoxEm5hrjq
NNTP-Posting-Host	223.178.213.71
References	<F285471C-E490-431C-9C00-635D9DFABA7C@microsoft.com> <u39fqYCPKHA.1372@TK2MSFTNGP02.phx.gbl>
User-Agent	G2/1.0
MIME-Version	1.0
Message-ID	<b836b82c-465e-43c0-995f-a4baeff929e8n@googlegroups.com> (permalink)
Subject	Re: Row_Number() in UPDATE statement
From	amita Gupta <amitagupta105@gmail.com>
Injection-Date	Sat, 16 Jul 2022 11:44:06 +0000
Content-Type	text/plain; charset="UTF-8"
Lines	34
Xref	csiph.com microsoft.public.sqlserver.programming:31380

Show key headers only | 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