row_number ()를 사용한 SQL 업데이트

row_number ()를 사용한 SQL 업데이트

---

CODE_DEST 열을 증분 번호로 업데이트하고 싶습니다. 나는 가지고있다:

CODE_DEST   RS_NOM
null        qsdf
null        sdfqsdfqsdf
null        qsdfqsdf

다음과 같이 업데이트하고 싶습니다.

CODE_DEST   RS_NOM
1           qsdf
2           sdfqsdfqsdf
3           qsdfqsdf

이 코드를 시도했습니다.

UPDATE DESTINATAIRE_TEMP
SET CODE_DEST = TheId 
FROM (SELECT  Row_Number()   OVER (ORDER BY [RS_NOM]) AS TheId FROM DESTINATAIRE_TEMP)

이것은 때문에 작동하지 않습니다 )

나는 또한 시도했다 :

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

그러나 이것은 또한 결합 때문에 작동하지 않습니다.

ROW_NUMBER()SQL Server 2008 R2 의 함수를 사용하여 열을 업데이트하려면 어떻게 해야합니까?

---

하나 더 옵션

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

---

DECLARE @id INT 
SET @id = 0 
UPDATE DESTINATAIRE_TEMP
SET @id = CODE_DEST = @id + 1 
GO 

이 시도

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

---

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

---

두 번째 시도는 기본 테이블과 동일한 CTE 이름을 지정하고 본질적으로 자체를 참조했기 때문에 CTE 가 재귀 CTE 인 것처럼 보이게했기 때문에 실패했습니다 . 재귀 CTE는 의 사용을 요구하는 특정 구조 있어야 UNION ALL집합 연산자.

Instead, you could just have given the CTE a different name as well as added the target column to it:

With SomeName As
(
SELECT 
CODE_DEST,
ROW_NUMBER() OVER (ORDER BY [RS_NOM] DESC) AS RN
FROM DESTINATAIRE_TEMP
)
UPDATE SomeName SET CODE_DEST=RN

---

This is a modified version of @Aleksandr Fedorenko's answer adding a WHERE clause:

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
WHERE x.CODE_DEST <> x.New_CODE_DEST AND x.CODE_DEST IS NOT NULL

By adding a WHERE clause I found the performance improved massively for subsequent updates. Sql Server seems to update the row even if the value already exists and it takes time to do so, so adding the where clause makes it just skip over rows where the value hasn't changed. I have to say I was astonished as to how fast it could run my query.

Disclaimer: I'm no DB expert, and I'm using PARTITION BY for my clause so it may not be exactly the same results for this query. For me the column in question is a customer's paid order, so the value generally doesn't change once it is set.

Also make sure you have indexes, especially if you have a WHERE clause on the SELECT statement. A filtered index worked great for me as I was filtering based on payment statuses.

---

My query using PARTITION by

UPDATE  UpdateTarget
SET     PaidOrderIndex = New_PaidOrderIndex
FROM
(
    SELECT  PaidOrderIndex, SimpleMembershipUserName, ROW_NUMBER() OVER(PARTITION BY SimpleMembershipUserName ORDER BY OrderId) AS New_PaidOrderIndex
    FROM    [Order]
    WHERE   PaymentStatusTypeId in (2,3,6) and SimpleMembershipUserName is not null
) AS UpdateTarget

WHERE UpdateTarget.PaidOrderIndex <> UpdateTarget.New_PaidOrderIndex AND UpdateTarget.PaidOrderIndex IS NOT NULL

-- test to 'break' some of the rows, and then run the UPDATE again
update [order] set PaidOrderIndex = 2 where PaidOrderIndex=3

The 'IS NOT NULL' part isn't required if the column isn't nullable.

---

When I say the performance increase was massive I mean it was essentially instantaneous when updating a small number of rows. With the right indexes I was able to achieve an update that took the same amount of time as the 'inner' query does by itself:

  SELECT  PaidOrderIndex, SimpleMembershipUserName, ROW_NUMBER() OVER(PARTITION BY SimpleMembershipUserName ORDER BY OrderId) AS New_PaidOrderIndex
    FROM    [Order]
    WHERE   PaymentStatusTypeId in (2,3,6) and SimpleMembershipUserName is not null

---

Simple and easy way to update the cursor

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

---

I did this for my situation and worked

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

---

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.

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

참고URL : https://stackoverflow.com/questions/13648898/sql-update-with-row-number