blocks|key|207671|text|With+UpdateData++As
(
SELECT+RS_NOM,
ROW_NUMBER()+OVER+(ORDER+BY+[RS_NOM]+DESC)+AS+RN
FROM+DESTINATAIRE_TEMP
)
UPDATE+DESTINATAIRE_TEMP+SET+CODE_DEST+=+RN
FROM+DESTINATAIRE_TEMP
INNER+JOIN+UpdateData+ON+DESTINATAIRE_TEMP.RS_NOM+=+UpdateData.RS_NOM|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|207672|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>With UpdateData As
(
SELECT RS_NOM,
ROW_NUMBER() OVER (ORDER BY [RS_NOM] DESC) AS RN
FROM DESTINATAIRE_TEMP
)
UPDATE DESTINATAIRE_TEMP SET CODE_DEST = RN
FROM DESTINATAIRE_TEMP
INNER JOIN UpdateData ON DESTINATAIRE_TEMP.RS_NOM = UpdateData.RS_NOM
</code></pre>

blocks|key|207701|text|再多一个选择|type|unstyled|depth|inlineStyleRanges|entityRanges|data|207702|UPDATE+x
SET+x.CODE_DEST+=+x.New_CODE_DEST
FROM+(
++++++SELECT+CODE_DEST,+ROW_NUMBER()+OVER+(ORDER+BY+[RS_NOM])+AS+New_CODE_DEST
++++++FROM+DESTINATAIRE_TEMP
++++++)+x|code-block|syntax|javascript|207703|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

One more option
<pre><code>UPDATE x
SET x.CODE_DEST = x.New_CODE_DEST
FROM (
 SELECT CODE_DEST, ROW_NUMBER() OVER (ORDER BY [RS_NOM]) AS New_CODE_DEST
 FROM DESTINATAIRE_TEMP
 ) x
</code></pre>

blocks|key|425850|text|DECLARE+@id+INT+
SET+@id+=+0+
UPDATE+DESTINATAIRE_TEMP
SET+@id+=+CODE_DEST+=+@id+%2B+1+
GO+|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|425851|尝尝这个|unstyled|425852|http://www.mssqltips.com/sqlservertip/1467/populate-a-sql-server-column-with-a-sequential-number-not-using-an-identity/|offset|length|425853|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|0|3B|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|S|8|@]|9|@]|A|$]]|$1|G|3|H|5|F|7|T|8|@]|9|@$I|U|J|V|1|W]]|A|$]]|$1|K|3|-4|5|F|7|X|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|H]]]]

<pre><code>DECLARE @id INT 
SET @id = 0 
UPDATE DESTINATAIRE_TEMP
SET @id = CODE_DEST = @id + 1 
GO 
</code></pre>

try this

<a href="http://www.mssqltips.com/sqlservertip/1467/populate-a-sql-server-column-with-a-sequential-number-not-using-an-identity/">http://www.mssqltips.com/sqlservertip/1467/populate-a-sql-server-column-with-a-sequential-number-not-using-an-identity/</a>

blocks|key|207762|text|我这样做是为了我的处境，并且工作。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|207763|WITH+myUpdate+(id,+myRowNumber+)
AS
(+
++++SELECT+id,+ROW_NUMBER()+over+(order+by+ID)+As+myRowNumber
++++FROM+AspNetUsers
++++WHERE++UserType='Customer'+
+)

update+AspNetUsers+set+EmployeeCode+=+FORMAT(myRowNumber,'00000#')+
FROM+myUpdate
++++left+join+AspNetUsers+u+on+u.Id=myUpdate.id|code-block|syntax|javascript|207764|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I did this for my situation and worked

<pre><code>WITH myUpdate (id, myRowNumber )
AS
( 
 SELECT id, ROW_NUMBER() over (order by ID) As myRowNumber
 FROM AspNetUsers
 WHERE UserType='Customer' 
 )

update AspNetUsers set EmployeeCode = FORMAT(myRowNumber,'00000#') 
FROM myUpdate
 left join AspNetUsers u on u.Id=myUpdate.id
</code></pre>

blocks|key|426015|text|在我的例子中，我添加了一个新列，并希望用整个表的equevilat记录号来更新它|type|unstyled|depth|inlineStyleRanges|entityRanges|data|426016|id++name+++++++new_column+(ORDER_NUM)
1+++Ali++++++++null
2+++Ahmad++++++null
3+++Mohammad+++null
4+++Nour+++++++null
5+++Hasan++++++null
6+++Omar+++++++null|code-block|syntax|javascript|426017|我编写了这个查询，以便用行号填充新列|426018|UPDATE+My_Table
SET+My_Table.ORDER_NUM++=+SubQuery.rowNumber
FROM+(
+++++++++SELECT+id+,ROW_NUMBER()+OVER+(ORDER+BY+[id])+AS+rowNumber
+++++++++FROM+My_Table
+++++)+SubQuery
INNER+JOIN+My_Table+ON
++++++++SubQuery.id+=+My_Table.id|426019|执行此查询后，我有1,2,3，...我的新专栏中的数字|426020|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

In my case I added a new column and wanted to update it with the equevilat record number for the whole table
<pre><code>id name new_column (ORDER_NUM)
1 Ali null
2 Ahmad null
3 Mohammad null
4 Nour null
5 Hasan null
6 Omar null
</code></pre>
I wrote this query to have the new column populated with the row number
<pre><code>UPDATE My_Table
SET My_Table.ORDER_NUM = SubQuery.rowNumber
FROM (
 SELECT id ,ROW_NUMBER() OVER (ORDER BY [id]) AS rowNumber
 FROM My_Table
 ) SubQuery
INNER JOIN My_Table ON
 SubQuery.id = My_Table.id
</code></pre>
after executing this query I had 1,2,3,... numbers in my new column

blocks|key|425830|text|更新游标的简单方法|type|unstyled|depth|inlineStyleRanges|entityRanges|data|425831|UPDATE+Cursor
SET+Cursor.CODE+=+Cursor.New_CODE
FROM+(
++SELECT+CODE,+ROW_NUMBER()+OVER+(ORDER+BY+[CODE])+AS+New_CODE
++FROM+Table+Where+CODE+BETWEEN+1000+AND+1999
++)+Cursor|code-block|syntax|javascript|425832|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Simple and easy way to update the cursor

<pre><code>UPDATE Cursor
SET Cursor.CODE = Cursor.New_CODE
FROM (
 SELECT CODE, ROW_NUMBER() OVER (ORDER BY [CODE]) AS New_CODE
 FROM Table Where CODE BETWEEN 1000 AND 1999
 ) Cursor
</code></pre>

blocks|key|425970|text|如果表没有关系，只需复制带有行号的新表中的所有内容，并删除旧表并用旧表重命名新表即可。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|425971|Select+++RowNum+=+ROW_NUMBER()+OVER(ORDER+BY(SELECT+NULL))+,+*+INTO+cdm.dbo.SALES2018+from+
(
select+*+from+SALE2018)+as+SalesSource|code-block|syntax|javascript|425972|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

If table does not have relation, just copy all in new table with row number and remove old and rename new one with old one.

<pre><code>Select RowNum = ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) , * INTO cdm.dbo.SALES2018 from 
(
select * from SALE2018) as SalesSource
</code></pre>

blocks|key|207900|text|我用第一次出现的part更新了一个临时表，其中多个part可以与一个序列号相关联。RowId=1返回我使用零件和序列号连接临时表格和数据的第一个实例。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|207901|update+#Tmp
set+
#Tmp.Amount=@Amount
from+
(SELECT+Part,+Row_Number()+++OVER+(ORDER+BY+[Part])+AS+RowId+FROM+#Tmp
where+Sequence_Num=@Sequence_Num
)data
where+data.Part=#Tmp.Part
and+data.RowId=1
and+#Tmp.Sequence_Num=@Sequence_Num|code-block|syntax|javascript|207902|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I update a temp table with the first occurrence of part where multiple parts can be associated with a sequence number. RowId=1 returns the first occurence which I join the tmp table and data using part and sequence number.
<pre><code>update #Tmp
set 
#Tmp.Amount=@Amount
from 
(SELECT Part, Row_Number() OVER (ORDER BY [Part]) AS RowId FROM #Tmp
where Sequence_Num=@Sequence_Num
)data
where data.Part=#Tmp.Part
and data.RowId=1
and #Tmp.Sequence_Num=@Sequence_Num
</code></pre>

blocks|key|426120|text|我没有跑步ID来做"Basheer+AL-MOMANI“建议的事情。我这样做了：(我自己加入了我的表，只是为了获得行号)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|426121|update+T1+set+inID+=+T2.RN
from+(select+*,+ROW_NUMBER()+over+(order+by+ID)+RN+from+MyTable)+T1
++++inner+join+(select+*,+ROW_NUMBER()+over+(order+by+ID)+RN+from+MyTable)+T2+on+T2.RN+=+T1.RN|code-block|syntax|javascript|426122|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I don't have a running ID in order to do what &quot;Basheer AL-MOMANI&quot; suggested.
I did something like this: (joined my table on myself, just to get the Row Number)
<pre><code>update T1 set inID = T2.RN
from (select *, ROW_NUMBER() over (order by ID) RN from MyTable) T1
 inner join (select *, ROW_NUMBER() over (order by ID) RN from MyTable) T2 on T2.RN = T1.RN
</code></pre>

I want to update my column CODE_DEST with an incremental number. I have:

<pre><code>CODE_DEST RS_NOM
null qsdf
null sdfqsdfqsdf
null qsdfqsdf
</code></pre>

I would like to update it to be:

<pre><code>CODE_DEST RS_NOM
1 qsdf
2 sdfqsdfqsdf
3 qsdfqsdf
</code></pre>

I have tried this code:

<pre><code>UPDATE DESTINATAIRE_TEMP
SET CODE_DEST = TheId 
FROM (SELECT Row_Number() OVER (ORDER BY [RS_NOM]) AS TheId FROM DESTINATAIRE_TEMP)
</code></pre>

This does not work because of the <code>)</code>

I have also tried:

<pre><code>WITH DESTINATAIRE_TEMP AS
 (
 SELECT 
 ROW_NUMBER() OVER (ORDER BY [RS_NOM] DESC) AS RN
 FROM DESTINATAIRE_TEMP
 )
UPDATE DESTINATAIRE_TEMP SET CODE_DEST=RN
</code></pre>

But this also does not work because of union.

How can I update a column using the <code>ROW_NUMBER()</code> function in SQL Server 2008 R2?

SQL Update with row_number()

我想用一个递增的数字更新我的列CODE_DEST。我有：CODE_DEST   RS_NOMnull        qsdfnull        sdfqsdfqsdfnull        qsdfqsdf我想将其更新为：CODE_DEST   RS_NOM1           qsdf2           sdfqsdfqsdf3           qsdfqsdf我已经尝试过以下代码

问使用row_number()进行SQL Update
EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用row_number()进行SQL UpdateEN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用row_number()进行SQL Update
EN