Обновите все кроме одной повторяющейся записи в таблице в SQL Server

Question

У меня есть таблица SQL Server, которая имеет повторяющиеся записи в одном из столбцов (object_id) например:

+----+-----------+------------+
| id | object_id | status_val |
+----+-----------+------------+
|  1 |         1 |          0 | 
|  2 |         1 |          0 | 
|  3 |         1 |          0 | 
|  4 |         2 |          0 | 
|  5 |         3 |          0 | 
|  6 |         3 |          0 | 
+----+-----------+------------+

Мне нужно обновить все их статусы, кроме одного, когда есть дублирование в object_id колонка. Так в таблице выше object_id 1 и 3 дублируются. Поэтому я хотел бы изменить их status_val до 2, кроме одной из записей. Результат будет выглядеть так:

| id | object_id | status_val |
+----+-----------+------------+
|  1 |         1 |          0 | 
|  2 |         1 |          2 | 
|  3 |         1 |          2 | 
|  4 |         2 |          0 | 
|  5 |         3 |          0 | 
|  6 |         3 |          2 | 
+----+-----------+------------+

Неважно, какая из дублированных строк имеет обновленный статус.

Любая помощь будет оценена.

Answer 1

Вы можете решить эту проблему без объединения, что означает, что она должна иметь лучшую производительность. Идея состоит в том, чтобы сгруппировать данные по вашему object_id, посчитав номер строки каждого object_id. Это то, что делает "разделение". Затем вы можете обновить, где row_num> 1. Это обновит все дубликаты object_id, кроме первого!

update t set t.status_val = 'some_status' 
from (
    select *, row_number() over(partition by object_id order by (select null)) row_num  
    from foo
) t 
where row_num > 1 

На тестовой таблице из 82944 записей производительность была такой (ваш пробег может отличаться!): Таблица "тест". Сканирование 5, логическое чтение 82283, физическое чтение 0, чтение с опережением 0, логическое чтение с бита 0, физическое чтение с бита 0, чтение с опережением чтения 0. Время ЦП = 141 мс, истекшее время = 150 мс.

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

Таблица "Тест". Сканирование 10, логические операции чтения 83622, физические операции чтения 0, операции чтения с опережением 0, логические операции чтения 0, физические операции чтения 0, математические операции чтения 0. Таблица "Рабочий файл". Сканирование счетчик 0, логическое чтение 0, физическое чтение 0, чтение с опережением 0, логическое чтение с бита 0, физическое чтение с бита 0, чтение с опережением чтения 0. Таблица "Рабочий стол". Сканирование 4, логическое чтение 167426, физическое чтение 0, чтение с опережением 0, чтение логического объекта 0, чтение с физического объекта 0, чтение с опережением 0. Время процессора = 342 мс, истекшее время = 233 мс.

Чтобы просмотреть результаты и обновить их небольшими партиями:

declare @rowcount int = 1;
declare @batch_size int = 1000;

while @rowcount > 0 
begin
    update top(@batch_size) t set t.status_val = 'already updated'
    from (
        select *, row_number() over(partition by object_id order by (select null)) row_num  
        from foo
        where status_val <> 'already updated' 
    ) t 
    where row_num > 1 
    set @rowcount = @@rowcount;
end

Это поможет сохранить блокировку, если другие параллельные сеансы пытаются получить доступ к этой таблице.

Answer 2

Согласно вашему вопросу, кажется, что для каждого значения object_id вы хотите сохранить status_val = 0 для object_id с наименьшим идентификатором и = 2 для остальных. Если это действительно так, и если object_id повторяется только максимум 3 раза, то у меня есть очень простое решение для вас. Используйте оператор по модулю или остатку, чтобы получить то, что вы хотите. Вот ответ, который я объясню позже:

update [MyTable]
set status_val = 2
where (id%3) != 1

Когда вы делите любое значение id на 3 остаток может быть только 0,1 или 2. Таким образом, для каждого object_id, где id%3 не равен 1, мы меняем status_val на 2.

Перед выполнением вышеприведенного кода, посмотрите вывод этого запроса -

select id, (id%3) as flg, object_id, status_val 
from MyTable

Answer 3

UPDATE Table
SET Table.status_val = '2'
FROM Table
INNER JOIN
(SELECT id, row_number()OVER(PARTITION BY object_id ORDER BY id) as seq FROM Table) other_table
ON Table.id = other_table.id AND seq <> 1