如何从表中删除重复的行？我想在我的表中保留一行与重复行。 [英] How to delete duplicate row from table ? and i want to keep one row from duplicate row in my table.

问题描述

我有一张像.....这样的表我要从表中删除数据。

i have a table like..... i want to delete data from table.

id	name	salary	department	departmentName
--------------------------------------------------------------------
1017	mukesh	12400	10	It
1017	mukesh	12400	10	It
1001	mukesh kumar bhagat	10000	10	IT
1002	Devendra	15000	20	MANAGEMENT
1003	manikant	15000	20	MANAGEMENT
1005	sony jha	15000	30	sales
1007	murli manohar	150000	10	IT
1008	rakesh	12400	20	MANAGEMENT
1009	deepak jha	121000	30	sales
1010	soni kumari	10000	30	sales
1016	mukesh	120000	10	IT

1.i想在表格中保留唯一的价值..

2.我想在表格中保留一个重复的行。



请帮助任何一个..

1.i want to keep only unique value in table..
2. i want to keep one duplicate row in the table.

please help any one..

推荐答案

如果你的表包含实际上相同的重复值，那么几乎不可能只删除一个，而你已经创建了一个基本设计数据库中的缺陷 - 行应至少有一个唯一值，以便您识别它们 - 通常是ID字段。



考虑重新设计：将ID设为身份字段，让数据库分配值而不是自己动手。

然后，复制从旧表（或旧数据库）到新表中的所有记录，在发生时更改ID。



否则，我怀疑你会这样做再次，一次又一次......

If your table contains duplicate values that are actually identical, then it is pretty much impossible to delete just one, and you have made a fundamental design flaw in your DB - rows should have at least one unique value to allow you to identify them - normally the ID field.

Consider a redesign: make the ID an identity field and let the db assign the values instead of doing it yourself.
Then, copy all the records from your old table (or old DB) into your new ones, handling the id changes as they happen.

Otherwise, I suspect you will be doing this again, and again, and again...

1）您可以通过创建 UNIQUE CONSTRAINT [ ^ ]到整行 - 但要注意以下几点：

a）它可能是资源昂贵的/>
b）为插入创建存储过程（或客户端代码）更便宜，在插入之前检查重复

c）它不会阻止用户输入不匹配。为避免这种情况，请在客户端使用规范化和下拉菜单



2）您可以使用 SELECT DISTINCT [ ^ ]子句选择唯一的行。您可以使用临时表来选择不同的行，然后删除原始表并重新插入临时表数据。但是如果你有一些指向表的引用约束，你必须暂时禁用它们（特别是ON DELETE CASCADE）。



我的建议：

A）规范你的桌子（在几个点上）

B）使用一些逻辑插入前避免重复

1) You can enforce unique rows, by creating a UNIQUE CONSTRAINT[^] to the whole row - but be aware of the followings:
a) it might be resource expensive
b) it is cheaper make a stored procedure (or client side code) for the insert, that checks for duplicate before inserting
c) it won''t prevent user input mismatches. To avoid that, use normalization and a dropdown on client side

2) You can use SELECT DISTINCT[^] clause to select unique rows. You can use a temporary table to select the distinct rows into it, and than delete the original table and insert the temporary table data back. But if you have some referential constraints pointing to the table, you will have to disable them temporary (especially the ON DELETE CASCADE ones).

My advice:
A) normalize your tables (on several points)
B) use some logic to avoid duplication before inserting

亲爱的@OriginalGriff，@Zoltán



我改变了我的桌子。添加标识列。喜欢..



名称薪水部门部门名称开斋节

mukesh 12400 10它1

mukesh 12400 10 It 2

mukesh 10000 10 IT 3

Deven 15000 20 MGT 4

manik 15000 20 MGT 5


所以，请帮忙解决这个问题。

Dear @OriginalGriff , @Zoltán

I have altered My table. add a identity column. like..

name salary department departmentName eid
mukesh 12400 10 It 1
mukesh 12400 10 It 2
mukesh 10000 10 IT 3
Deven 15000 20 MGT 4
manik 15000 20 MGT 5

so, pls help to solve this.

这篇关于如何从表中删除重复的行？我想在我的表中保留一行与重复行。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！