Я хочу обновить таблицу с последовательной нумерацией, начиная с 1. В обновлении есть предложение where, поэтому будут перенумерованы только результаты, которые удовлетворяют этому предложению. Могу ли я выполнить это эффективно, не используя временную таблицу?
Обновление SQL с последовательной нумерацией
Ответ 1
Это, вероятно, зависит от вашей базы данных, но вот решение для MySQL 5, которое включает в себя использование переменной:
SET @a:=0;
UPDATE table SET [email protected]:[email protected]+1 WHERE whatever='whatever' ORDER BY field2,field3
Вероятно, вы должны изменить свой вопрос и указать, какую базу данных вы используете.
Изменить: я нашел решение, используя T-SQL для SQL Server. Он очень похож на метод MySQL:
DECLARE @myVar int
SET @myVar = 0
UPDATE
myTable
SET
@myvar = myField = @myVar + 1
Ответ 2
Для Microsoft SQL Server 2005/2008. Функция ROW_NUMBER() была добавлена в 2005 году.
; with T as (select ROW_NUMBER() over (order by ColumnToOrderBy) as RN
, ColumnToHoldConsecutiveNumber from TableToUpdate
where ...)
update T
set ColumnToHoldConsecutiveNumber = RN
РЕДАКТИРОВАТЬ: Для SQL Server 2000:
declare @RN int
set @RN = 0
Update T
set ColumnToHoldConsecutiveNubmer = @RN
, @RN = @RN + 1
where ...
ПРИМЕЧАНИЕ. Когда я тестировал, увеличение @RN происходило до установки в столбце @RN, поэтому приведенное выше дает числа, начинающиеся с 1.
РЕДАКТИРОВАТЬ: я только что заметил, что, кажется, вы хотите создать несколько последовательных чисел в таблице. В зависимости от требований, вы можете сделать это за один проход с SQL Server 2005/2008, добавив partition by
к предложению over
:
; with T as (select ROW_NUMBER()
over (partition by Client, City order by ColumnToOrderBy) as RN
, ColumnToHoldConsecutiveNumber from TableToUpdate)
update T
set ColumnToHoldConsecutiveNumber = RN
Ответ 3
В оракуле это работает:
update myTable set rowColum = rownum
where something = something else
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/pseudocolumns009.htm#i1006297
Ответ 4
Помимо использования CTE или WITH, также возможно использовать обновление с самосоединением в той же таблице:
UPDATE a
SET a.columnToBeSet = b.sequence
FROM tableXxx a
INNER JOIN
(
SELECT ROW_NUMBER() OVER ( ORDER BY columnX ) AS sequence, columnY, columnZ
FROM tableXxx
WHERE columnY = @groupId AND columnY = @lang2
) b ON b.columnY = a.columnY AND b.columnZ = a.columnZ
Полученная таблица, псевдоним b, используется для создания последовательности через функцию ROW_NUMBER() вместе с некоторыми другими столбцами, которые образуют виртуальный первичный ключ. Как правило, для каждой строки требуется уникальное значение последовательности.
Предложение WHERE является необязательным и ограничивает обновление для тех строк, которые удовлетворяют указанным условиям.
Затем производная таблица соединяется с той же таблицей, псевдоним a, соединяется в столбцах виртуального первичного ключа с обновляемым столбцом, установленным в сгенерированную последовательность.
Ответ 5
Если вы хотите создать новый столбец PrimaryKey, используйте только это:
ALTER TABLE accounts ADD id INT IDENTITY(1,1)
Ответ 6
Я использовал эту технику в течение многих лет для заполнения ординалов и последовательно пронумерованных столбцов. Однако недавно я обнаружил проблему с ним при работе на SQL Server 2012. По-видимому, внутренне механизм запросов применяет обновление с использованием нескольких потоков, а предикатная часть UPDATE не обрабатывается поточно-безопасным способом. Чтобы заставить его работать снова, мне пришлось перенастроить максимальную степень SQL Server parallelism до 1 ядра.
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'max degree of parallelism', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
DECLARE @id int
SET @id = -1
UPDATE dbo.mytable
SET @id = Ordinal = @id + 1
Без этого вы обнаружите, что большинство последовательных номеров дублируются по всей таблице.
Ответ 7
Чтобы получить пример работы Шеннона, я должен был отредактировать его ответ:
; WITH CTE AS (
SELECT ROW_NUMBER() OVER (ORDER BY [NameOfField]) as RowNumber, t1.ID
FROM [ActualTableName] t1
)
UPDATE [ActualTableName]
SET Name = 'Depersonalised Name ' + CONVERT(varchar(255), RowNumber)
FROM CTE
WHERE CTE.Id = [ActualTableName].ID
поскольку его ответ пытался обновить T, что в его случае было именем Common Table Expression, и оно выдает ошибку.
Ответ 8
Присоединиться к таблице чисел? Он включает в себя дополнительную таблицу, но она не будет временной - вы сохраните таблицу чисел в качестве утилиты.
или же
http://www.sqlservercentral.com/articles/Advanced+Querying/2547/
(последнее требует бесплатной регистрации, но я считаю, что это очень хороший источник советов и методов для MS SQL Server, и многое применимо к любой реализации SQL).
Ответ 9
Возможно, но только через некоторые очень сложные запросы - в основном вам нужен подзапрос, который подсчитывает количество записей, выбранных до сих пор, и использует это как идентификатор последовательности. В какой-то момент я написал что-то подобное - это сработало, но было много боли.
Если честно, вам будет лучше с временной таблицей с полем автоинкремента.