SQL删除具有相同ID的记录,只留下1
问题描述:
奇怪的问题,我知道。我不想删除所有行并重新开始,但我们有一个开发数据库表,其中一些行具有重复的ID,但具有不同的值。SQL删除具有相同ID的记录,只留下1
我想删除所有具有重复ID的记录,这样我就可以强制在表上为新版本建立关系的数据完整性。目前它是一个由代码插入和生成的ID(遗留)。
从另一个问题,我得到这个:
delete
t1
from
tTable t1, tTable t2
where
t1.locationName = t2.locationName and
t1.id > t2.id
但是,这将无法正常工作的ID都是一样的!
如何删除ID相同的一条记录?即,删除具有相同ID> 1的记录的计数的位置?如果这是不可能的,那么删除所有具有重复ID的记录就可以。
答
在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
答
取决于您的数据库服务器,但您可以关联DELETE和LIMIT(mysql)或TOP(sql server)。
您也可以将每个记录的第一个(不重复)移动到临时表中,删除原始表并将临时表复制回原始表。
答
不知道为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]
希望这有助于。
你使用哪个dbms? – 2011-04-21 11:13:00
@Ash sql server 2005 – 2011-04-21 11:18:20
你想摆脱'id'或'locationName'上的重复吗? – Quassnoi 2011-04-21 11:28:11