В настоящее время вы работаете в автономном режиме; ожидается повторное подключение к Интернету

Удаление повторяющихся строк из таблицы в SQL Server

Аннотация
Таблицы Microsoft SQL Server никогда не должны содержать повторяющихся строк инеуникальных первичных ключей. Для краткости в этой статье мы будем иногда называть первичныеключи просто ключами, но подразумеваться всегда будут именно первичныеключи. Повторяющиеся ключи нарушают целостность сущностей, поэтомудолжны быть запрещены в реляционной системе. SQL Server содержит ряд механизмов,обеспечивающих целостность сущностей, включая индексы, ограничения UNIQUE,ограничения PRIMARY KEY и триггеры.

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

Повторяющиеся ключи часто обнаруживаются при попытке создать уникальный индекс.Если они имеются, эта операция прерывается. При этом выводится следующее сообщение.
Msg 1505, Level 16, State 1Create unique index aborted on duplicate key.
Если используется SQL Server 2000 или SQL Server 2005, может быть выведено следующее сообщение об ошибке.
Сообщение 1505, уровень 16, состояние 1 Операция CREATE UNIQUE INDEX прервана, так как обнаружен повторяющийся ключ для имени объекта "%1!s!" и имени индекса "%2!s!". Повторяющееся значение ключа: %ls.
В данной статье рассматривается процедура обнаружения и удаления повторяющихся первичных ключейиз таблицы. Однако при обнаружении повторяющихся ключей следует также тщательно изучить процедуру их появления,чтобы предотвратить возникновение этой проблемы в будущем.

Дополнительная информация
Проблема дублирования ключей будет рассмотрена на примере следующей таблицы с повторяющимся первичным ключом.В данной таблице первичным ключом являются два столбца (col1, col2). Создатьуникальный индекс или ограничение PRIMARY KEY в данном случае невозможно, поскольку две строки имеютповторяющиеся первичные ключи. Можно воспользоваться процедурой, описанной ниже, чтобы обнаружить и удалитьдубликаты.
create table t1(col1 int, col2 int, col3 char(50))insert into t1 values (1, 1, 'data value one')insert into t1 values (1, 1, 'data value one')insert into t1 values (1, 2, 'data value two')				
Первым делом следует определить, какие строки имеют повторяющиеся значения первичного ключа.
SELECT col1, col2, count(*)FROM t1GROUP BY col1, col2HAVING count(*) > 1				
Этот код возвращает одну строку для каждого набора повторяющихся значений первичного ключа в таблице.Последний столбец в полученном результате представляет число дубликатовопределенного значения первичного ключа.

col1col2
112


Если повторяющихся значений первичного ключа мало, лучше всегоудалить их по отдельности вручную. например:
set rowcount 1delete from t1where col1=1 and col2=1				
Значение rowcount должно быть на единицу меньше, чем число дубликатов определенного значения ключа. В данном примере дубликатов два, поэтому переменной rowcount присваивается значение 1. Значения col1 и col2 берутся из результата выполнения запроса GROUP BY, приведенного выше. Если запрос GROUP BY возвращает несколько строк, запрос «set rowcount» нужно будет выполнить по одному разу для каждой из этих строк. Переменной rowcount при каждом запуске нужно должно присваиваться значение на единицу меньше числа дубликатов определенного значения первичного ключа.

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

Если таблица содержит большое число разных наборов повторяющихся значений первичного ключа,на их удаление вручную может потребоваться слишком много времени. В этом случаеможно воспользоваться нижеуказанной процедурой.
  1. Сначала выполните приведенный выше запрос GROUP BY, чтобы узнать количество наборов повторяющихся значений первичного ключа и число дубликатов в каждом наборе.
  2. Выделите повторяющиеся значения ключа в отдельную таблицу, например:
    SELECT col1, col2, col3=count(*)INTO holdkeyFROM t1GROUP BY col1, col2HAVING count(*) > 1					
  3. Выделите повторяющиеся строки в отдельную таблицу, удаляя при этом дубликаты, например:
    SELECT DISTINCT t1.*INTO holddupsFROM t1, holdkeyWHERE t1.col1 = holdkey.col1AND t1.col2 = holdkey.col2					
  4. Теперь таблица holddups должна содержать уникальные первичные ключи, однако это условие не будет выполнено, если таблица t1 включает повторяющиеся первичные ключи и уникальные строки (как в приведенном выше примере с номерами социального обеспечения). Убедитесь в том, что каждый ключ в таблице holddups уникален и что у вас нет повторяющихся ключей при наличии уникальных строк. Если это так, подумайте, какие из строк нужно сохранить для определенного повторяющегося значения ключа. Например, запрос
    SELECT col1, col2, count(*)FROM holddupsGROUP BY col1, col2						
    должен вернуть для каждой строки значение счетчика, равное 1. Если это так, перейдите к действию 5. Если нет, повторяющиеся ключи имеются при наличии уникальных строк, и тогда нужно решить, какие строки следует сохранить. Как правило, при этом или строка удаляется, или для нее создается уникальное значение ключа. Сделайте или первое, или второе для каждого такого повторяющегося ключа в таблице holddups.
  5. Удалите повторяющиеся строки из исходной таблицы, например:
    DELETE t1FROM t1, holdkeyWHERE t1.col1 = holdkey.col1AND t1.col2 = holdkey.col2					
  6. Поместите уникальные строки обратно в исходную таблицу, например:
    INSERT t1 SELECT * FROM holddups					
sql6 dedupe entity intg
Свойства

Номер статьи: 139444 — последний просмотр: 06/30/2016 13:10:00 — редакция: 6.0

Microsoft SQL Server 6.0 Standard Edition, Microsoft SQL Server 6.5 Standard Edition, Microsoft SQL Server 7.0 Standard Edition, Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Express Edition, Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Workgroup Edition

  • kbsqlsetup kbinfo kbusage KB139444
Отзывы и предложения
t.createElement("meta"); m.content = guid; m.name = "ms.dqid"; document.getElementsByTagName("head")[0].appendChild(m);