Обновление SQL с помощью row_number()

Я хочу обновить столбец 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?

Ответы

Ответ 1

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

Ответ 2

Еще один вариант

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

Ответ 3

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/

Ответ 4

Ваша вторая попытка потерпела неудачу в первую очередь потому, что вы назвали CTE так же, как и базовую таблицу, и сделали CTE похожим, как если бы он был рекурсивным CTE, потому что он по существу ссылался на себя. A рекурсивный CTE должен иметь определенную структуру, которая требует использования оператора set UNION ALL.

Вместо этого вы могли бы просто присвоить CTE другое имя, а также добавить к нему целевой столбец:

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

Ответ 5

Это модифицированная версия @Aleksandr Fedorenko, отвечающая на добавление предложения WHERE:

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

Добавив предложение WHERE, я обнаружил, что производительность значительно улучшилась для последующих обновлений. Кажется, что сервер Sql обновляет строку, даже если значение уже существует, и для этого требуется время, поэтому добавление предложения where позволяет просто пропустить строки, где значение не изменилось. Должен сказать, я был удивлен, насколько быстро он может выполнить мой запрос.

Отказ от ответственности: я не эксперт по БД, и я использую PARTITION BY для моего предложения, так что это может быть не совсем то же самое для этого запроса. Для меня колонка, о которой идет речь, является платным заказом клиента, поэтому значение обычно не изменяется после его установки.

Также убедитесь, что у вас есть индексы, особенно если у вас есть предложение WHERE в инструкции SELECT. Отфильтрованный индекс работал отлично для меня, поскольку я фильтровался на основе статусов платежей.

Мой запрос с использованием PARTITION

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

Часть "NOT NOT" не требуется, если столбец не имеет значения NULL.

Когда я говорю, что увеличение производительности было массивным, я имею в виду, что это было существенно мгновенно при обновлении небольшого количества строк. При правильных индексах мне удалось добиться обновления, которое заняло тот же промежуток времени, что и внутренний запрос:

  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

Ответ 6

Простой и удобный способ обновления курсора

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

Ответ 7

Я сделал это для моей ситуации и работал

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

Ответ 8

Если таблица не имеет отношения, просто скопируйте все в новую таблицу с номером строки, удалите старую и переименуйте новую в старую.

Выберите RowNum = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), * INTO cdm.dbo.SALES2018 из (выберите * из SALE2018) в качестве SalesSource