Блог

Я пытаюсь исправить большое количество повторяющихся записей в SQL Server, есть ли способ сделать это как массовое обновление, а не запускать сценарий обновления для каждой строки?

Данные выглядят следующим образом:

 client | voucher_no | sequence_no | id
------- ------------ ------------- ------
AA     | 11111111   | 1           | 0001
AA     | 11111111   | 1           | 0002 
AA     | 11111111   | 1           | 0003
AA     | 11111112   | 13          | 0004
AA     | 11111112   | 13          | 0004
AA     | 11111113   | 28          | 0005
AA     | 11111113   | 28          | 0006
AA     | 11111114   | 17          | 0007
AA     | 11111114   | 17          | 0008
AA     | 11111115   | 23          | 0009
AA     | 11111115   | 23          | 0010
  

Я пытался найти способ обновить записи за один раз, чтобы результаты скрипта были:

 client | voucher_no | sequence_no | id
------- ------------ ------------- ------
AA     | 11111111   | 1           | 0001
AA     | 11111111   | 2           | 0002 
AA     | 11111111   | 3           | 0003
AA     | 11111112   | 13          | 0004
AA     | 11111112   | 14          | 0005
AA     | 11111113   | 28          | 0006
AA     | 11111113   | 29          | 0007
AA     | 11111114   | 17          | 0008
AA     | 11111114   | 18          | 0009
AA     | 11111115   | 23          | 0010
AA     | 11111115   | 24          | 0011


UPDATE table_a
SET sequence_no = (SELECT ROW_NUMBER() OVER (PARTITION BY sequence_no ORDER BY t.client, t.voucher_no, t.sequence_no ))
                   FROM table_a AS t
                   WHERE t.id IN (0002, 0003, 0005, 0007, 0009, 0011)
  

Вышеуказанное отлично работает для первого идентификатора в списке, но обновляет остальные записи с тем же sequence_no, что и первый.

Итак, я получаю:

 client | voucher_no | sequence_no | id
------- ------------ ------------- ------
AA     | 11111111   | 2           | 0002 
AA     | 11111111   | 2           | 0003
AA     | 11111112   | 2           | 0005
AA     | 11111113   | 2           | 0007
AA     | 11111114   | 2           | 0009
AA     | 11111115   | 2           | 0011
  

Что может вызвать дополнительные проблемы, если порядковый номер обновляется до того, который уже существует для этого клиента и комбинации voucher_no.

Я также попытался обернуть вышеуказанное в курсор, думая, что запрос не получает возможности обновить с помощью правильного sequence_no для следующего идентификационного номера, но это тоже не сработало

Мне удалось решить эту проблему, используя следующий код:

 DECLARE @client VARCHAR(2)
DECLARE @voucher_no INT 
DECLARE @sequence_no INT
DECLARE @id INT 


DECLARE cursor_results CURSOR FOR 
WITH T AS 
(
    SELECT *, COUNT(*) OVER (PARTITION BY client, voucher_no, sequence_no) AS cnt 
      FROM table_a 
)

  SELECT t.client, t.voucher_no, t.sequence_no,t.id
    FROM T AS t 
   WHERE t.cnt > 1
   AND agrtid IN (0002, 0003, 0005, 0007, 0009, 0011) 
ORDER BY t.client, t.voucher_no, t.sequence_no DESC 

OPEN cursor_results 
FETCH NEXT FROM cursor_results INTO @client, @voucher_no, @sequence_no, @id
WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @new_sequence_no INT = (SELECT MAX(sequence_no)   1 FROM table_a WHERE client = @client AND voucher_no = @voucher_no)
    UPDATE table_a
       SET sequence_no = @new_sequence_no,
           last_update = GETDATE(),
           user_id = 'USR' 
     WHERE id = @id 
       AND client = @client 
       AND voucher_no = @voucher_no

FETCH NEXT FROM cursor_results INTO @client, @voucher_no, @sequence_no, @id
END 

CLOSE cursor_results 
DEALLOCATE cursor_results
  

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

Установка @new_sequence_no таким образом также гарантировала, что я получаю правильное значение MAX(sequence_no) 1 для записи, в которой я был в то время, вместо того, чтобы всем дубликатам присваивалось значение MAX(sequence_no) 1 для первой записи.