SQL удаляет записи с тем же идентификатором, оставляя 1

Странный вопрос, я знаю. Я не хочу удалять все строки и начинать заново, но у нас есть таблица базы данных разработки, в которой некоторые строки имеют повторяющиеся идентификаторы, но разные значения.

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

Из другого вопроса я получил это:

delete 
   t1 
from 
   tTable t1, tTable t2 
where 
   t1.locationName = t2.locationName and  
   t1.id > t2.id

Но это не сработает, так как идентификаторы одинаковы!

Как я могу удалить все записи, кроме одной, с одинаковыми идентификаторами? То есть удалять там, где количество записей с одинаковым ID > 1? Если это невозможно, то можно удалить все записи с повторяющимися идентификаторами.


sql sql-delete
person Tom Gullen    schedule 21.04.2011    source источник
comment
Вы хотите избавиться от дубликатов на id или locationName?   -  person Quassnoi    schedule 21.04.2011

Ответы (3)


arrow_upward
1
arrow_downward

В SQL Server 2005 и выше:

WITH    q AS
        (
        SELECT  *,
                ROW_NUMBER() OVER (PARTITION BY locationName ORDER BY id) rn
        FROM    tTable
        )
DELETE
FROM    q
WHERE   rn > 1
person Quassnoi    schedule 21.04.2011

arrow_upward
1
arrow_downward

Зависит от вашего сервера БД, но вы можете связать DELETE и LIMIT (mysql) или TOP (сервер sql).

Вы также можете переместить первую (не дубликат) каждой записи во временную таблицу, удалить исходную таблицу и скопировать временную таблицу обратно в исходную.

person Vincent Mimoun-Prat    schedule 21.04.2011

arrow_upward
1
arrow_downward

Не уверен для mysql, но для базы данных MSServer вы можете использовать следующее

SET IDENTITY_INSERT [tablename] ON
SELECT DISTINCT col1, col2, col3 INTO temp_[tablename] FROM [tablename]
ALTER TABLE temp_[tablename] ADD IDcol INT IDENTITY
TRUNCATE TABLE [tablename]
INSERT INTO [tablename](IDcol, col1, col2, col3) SELECT IDcol, col1, col2, col3 FROM temp_[tablename]
DROP TABLE temp_[tablename]

Надеюсь это поможет.

person Ash Burlaczenko    schedule 21.04.2011