SQL 如何删除两个完全相同的行之一？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16438323/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I delete one of two perfectly identical rows?
提问by david.libremone
I am cleaning out a database table without a primary key (I know, I know, what were they thinking?). I cannot add a primary key, because there is a duplicate in the column that would become the key. The duplicate value comes from one of two rows that are in all respects identical. I can't delete the row via a GUI (in this case MySQL Workbench, but I'm looking for a database agnostic approach) because it refuses to perform tasks on tables without primary keys (or at least a UQ NN column), and I cannot add a primary key, because there is a duplicate in the column that would become the key. The duplicate value comes from one...
我正在清理没有主键的数据库表（我知道，我知道，他们在想什么？）。我无法添加主键，因为列中有一个重复项会成为键。重复值来自在所有方面都相同的两行之一。我无法通过 GUI 删除该行（在本例中为 MySQL Workbench，但我正在寻找一种与数据库无关的方法），因为它拒绝在没有主键（或至少是 UQ NN 列）的表上执行任务，并且我无法添加主键，因为列中有一个重复项会成为键。重复值来自一个...
How can I delete one of the twins?
如何删除双胞胎之一？
采纳答案by Alnitak
One option to solve your problem is to create a new table with the same schema, and then do:
解决问题的一种选择是创建一个具有相同架构的新表，然后执行以下操作：
INSERT INTO new_table (SELECT DISTINCT * FROM old_table)
and then just rename the tables.
然后只需重命名表。
You will of course need approximately the same amount of space as your table requires spare on your disk to do this!
当然，您需要的空间量与您的表需要磁盘上的备用空间大致相同！
It's not efficient, but it's incredibly simple.
它效率不高，但非常简单。
回答by Rinaldo
SET ROWCOUNT 1
DELETE FROM [table] WHERE ....
SET ROWCOUNT 0
This will only delete one of the two identical rows
这只会删除两行相同的行之一
回答by Piskvor left the building
Note that MySQL has its own extension of DELETE, which is DELETE ... LIMIT, which works in the usual way you'd expect from LIMIT: http://dev.mysql.com/doc/refman/5.0/en/delete.html
请注意，MySQL 有自己的 扩展名DELETE，即DELETE ... LIMIT，它以您期望的通常方式工作LIMIT：http: //dev.mysql.com/doc/refman/5.0/en/delete.html
The MySQL-specific LIMIT row_count option to DELETE tells the server the maximum number of rows to be deleted before control is returned to the client. This can be used to ensure that a given DELETE statement does not take too much time. You can simply repeat the DELETE statement until the number of affected rows is less than the LIMIT value.
DELETE 的 MySQL 特定 LIMIT row_count 选项告诉服务器在控制返回给客户端之前要删除的最大行数。这可用于确保给定的 DELETE 语句不会花费太多时间。您可以简单地重复 DELETE 语句，直到受影响的行数小于 LIMIT 值。
Therefore, you could use DELETE FROM some_table WHERE x="y" AND foo="bar" LIMIT 1;note that there isn't a simple way to say "delete everything except one" - just keep checking whether you still have row duplicates.
因此，您可以使用DELETE FROM some_table WHERE x="y" AND foo="bar" LIMIT 1;注意，没有一种简单的方法可以说“删除除一个之外的所有内容”-只需继续检查您是否仍然有重复的行。
回答by mattinsalto
delete top(1) works on Microsoft SQL Server (T-SQL).
delete top(1) 适用于 Microsoft SQL Server (T-SQL)。
回答by Turbut Alin
For PostgreSQL you can do this:
对于 PostgreSQL，您可以这样做：
DELETE FROM tablename
WHERE id IN (SELECT id
FROM (SELECT id, ROW_NUMBER()
OVER (partition BY column1, column2, column3 ORDER BY id) AS rnum
FROM tablename) t
WHERE t.rnum > 1);
column1, column2, column3 would the column set which have duplicate values.
column1, column2, column3 是具有重复值的列集。
Reference here.
参考这里。
回答by AHiggins
This can be accomplished using a CTE and the ROW_NUMBER()function, as below:
这可以使用 CTE 和ROW_NUMBER()函数来完成，如下所示：
/* Sample Data */
CREATE TABLE #dupes (ID INT, DWCreated DATETIME2(3))
INSERT INTO #dupes (ID, DWCreated) SELECT 1, '2015-08-03 01:02:03.456'
INSERT INTO #dupes (ID, DWCreated) SELECT 2, '2014-08-03 01:02:03.456'
INSERT INTO #dupes (ID, DWCreated) SELECT 1, '2013-08-03 01:02:03.456'
/* Check sample data - returns three rows, with two rows for ID#1 */
SELECT * FROM #dupes
/* CTE to give each row that shares an ID a unique number */
;WITH toDelete AS
(
SELECT ID, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DWCreated) AS RN
FROM #dupes
)
/* Delete any row that is not the first instance of an ID */
DELETE FROM toDelete WHERE RN > 1
/* Check the results: ID is now unique */
SELECT * FROM #dupes
/* Clean up */
DROP TABLE #dupes
Having a column to ORDER BY is handy, but not necessary unless you have a preference for which of the rows to delete. This will also handle all instances of duplicate records, rather than forcing you to delete one row at a time.
有一个 ORDER BY 列很方便，但不是必需的，除非您对要删除的行有偏好。这也将处理重复记录的所有实例，而不是强制您一次删除一行。
回答by Feelsbadman
Tried LIMIT 1? This will only delete 1 of the rows that match your DELETEquery
尝试限制 1？这只会删除与您的DELETE查询匹配的行中的 1 个
DELETE FROM `table_name` WHERE `column_name`='value' LIMIT 1;
回答by TIm
You could use a max, which was relevant in my case.
您可以使用最大值，这与我的情况相关。
DELETE FROM [table] where id in
(select max(id) from [table] group by id, col2, col3 having count(id) > 1)
Be sure to test your results first and having a limiting condition in your "having" clausule. With such a huge delete query you might want to update your database first.
一定要先测试你的结果，并在你的“拥有”条款中有一个限制条件。对于如此庞大的删除查询，您可能希望首先更新您的数据库。
回答by Vlad B
This works for PostgreSQL
这适用于 PostgreSQL
DELETE FROM tablename WHERE id = 123 AND ctid IN (SELECT ctid FROM tablename WHERE id = 123 LIMIT 1)
回答by Der U
in case you can add a column like
如果你可以添加一个列
ALTER TABLE yourtable ADD IDCOLUMN bigint NOT NULL IDENTITY (1, 1)
do so.
这样做。
then count rows grouping by your problem column where count >1 , this will identify your twins (or triplets or whatever).
然后计算按您的问题列分组的行，其中 count >1 ，这将识别您的双胞胎（或三胞胎或其他）。
then select your problem column where its content equals the identified content of above and check the IDs in IDCOLUMN.
然后选择您的问题列，其内容等于上述标识的内容，并检查 IDCOLUMN 中的 ID。
delete from your table where IDCOLUMN equals one of those IDs.
从您的表中删除 IDCOLUMN 等于这些 ID 之一。
