Почему это не работает для удаления дубликатов

#sql #sql-server #database

#sql #sql-сервер #База данных

Вопрос:

Я работал с MySQL, но теперь я пытаюсь выполнить аналогичные действия с MS SQL (2016), но запрос не работает. У меня есть таблица «TEST», и я пытаюсь удалить дубликаты на основе совпадения столбцов «lastname, firstname».

Использование наименьшего значения ID:

 DELETE FROM TEST
WHERE lastname NOT IN (
  SELECT t.id FROM ( 
    SELECT MIN(PersonID) id
    FROM TEST
    GROUP BY lastname, firstname
  ) t
)

Столбцы: «PersonID», «LastName», «FirstName», «Address», «City»

Ошибка:

Сообщение 245, уровень 16, состояние 1, преобразование строки 1 завершилось ошибкой при преобразовании значения varchar ‘JOHN’ в тип данных int.

1. Извините! Ошибка заключается в следующем: Сообщение 245, уровень 16, состояние 1, ошибка преобразования строки 1 при преобразовании значения varchar ‘JOHN’ в тип данных int.

Ответ №1:

Вы хотите, чтобы строка с минимальным идентификатором была правильной? Используйте EXISTS :

 delete t from test t
where exists (
  select 1 from test
  where firstname = t.firstname and lastname = t.lastname
  and id < t.id
)

1. Да, как и в первом появившемся, должен остаться тот, который останется. Я получаю «Сообщение 102, уровень 15, состояние 1, в строке 1 неправильный синтаксис рядом с ‘t'». . Я не совсем понимаю, почему.

2. Вы изменили на: delete t from test t ?

3. Вы правы, это работает, и я это понимаю. Спасибо!

Ответ №2:

Вы сравниваете фамилию с идентификатором, отсюда и ошибка, о которой вы сообщили «Ошибка преобразования при преобразовании значения varchar ‘JOHN’ в тип данных int» попробуйте это вместо:

 DELETE FROM TEST
WHERE PersonID NOT IN (
  SELECT t.id FROM ( 
    SELECT MIN(PersonID) id
    FROM TEST
    GROUP BY lastname, firstname
  ) t
)

Ответ №3:

В SQL Server я рекомендую использовать для этого оконные функции:

 with todelete as (
      select t.*,
             row_number() over (partition by firstname, lastname order by id) as seqnum
      from test t
     ) 
delete from todelete
    where seqnum > 1;

1. Это определенно работает, но я недостаточно разбираюсь в SQL Server, чтобы полностью понять это. Является ли это более эффективным подходом для описания того, как Forpas это делал? Спасибо!

2. @Natsu . . . Это интересный вопрос. Вам нужно будет протестировать это на ваших данных. В общем, я думаю, что это было бы быстрее, но при некоторых обстоятельствах exists предложение могло бы быть.

3. Это не позволит мне проголосовать за ваш ответ, потому что я все еще новичок, извините. Однако я очень ценю вашу помощь.

Ответ №4:

Вы можете попробовать преобразовать свой идентификатор в varchar / nvarchar, поскольку lastname это символ. Хотя вы можете дважды проверить, следует ли ссылаться на PersonID или lastname.

 DELETE FROM TEST
WHERE lastname NOT IN (
  SELECT CONVERT(VARCHAR(50),t.id) FROM ( 
    SELECT MIN(PersonID) id
    FROM TEST
    GROUP BY lastname, firstname
  ) t
)

Ответ №5:

Вы тоже можете попробовать этот подход :

 DELETE T2 FROM ( 
    SELECT 
        MIN(PersonID) id
    FROM 
        TEST
    GROUP BY 
        lastname, firstname
  ) t
INNER JOIN TEST T2 ON t.id = T2.PersonID