如何在MySQL表中删除副本

I've given a client the following query to delete duplicate phone no. records in an MSSQL database, but now they need to also do it on MySQL, and they report that MySQL complains about the format of the query. I've included the setup of a test table with duplicates for my code sample, but the actual delete query is what counts.

我已经给了客户以下的查询来删除重复的电话号码。在MSSQL数据库中记录，但是现在他们也需要在MySQL上做，并且他们报告MySQL抱怨查询的格式。我已经为我的代码示例设置了带有副本的测试表，但是实际的删除查询才是最重要的。

I'm asking this in ignorance and urgency, as I am still busy downloading and installing MySQL, and just maybe somebody can help in the mean time.

我出于无知和迫切性地问这个问题，因为我还在忙着下载和安装MySQL，也许有人可以帮忙。

 create table bkPhone
 (
     phoneNo nvarchar(20),
     firstName nvarchar(20),
     lastName nvarchar(20)
 )
 GO

 insert bkPhone values('0783313780','Brady','Kelly')
 insert bkPhone values('0845319792','Mark','Smith')
 insert bkPhone values('0834976958','Bill','Jones')
 insert bkPhone values('0845319792','Mark','Smith')
 insert bkPhone values('0828329792','Mickey','Mouse')
 insert bkPhone values('0834976958','Bill','Jones')

 alter table bkPhone add phoneId int identity

 delete from bkPhone
 where phoneId not in
 (
     select min(phoneId)
     from bkPhone
     group by phoneNo,firstName,lastName
     having  count(*) >= 1
 )

4 个解决方案

#1

Many ways lead to Rome. This is one. It is very fast. So you can use it with big databases. Don't forget the indeces. The trick is: make phoneNo unique and use "ignore".

通往罗马的道路很多。这是一个。这是非常快。所以你可以用大数据库。不要忘记indec。诀窍是:让phoneNo与众不同，并使用“忽略”。

drop table if exists bkPhone_template;
create table bkPhone_template (
         phoneNo varchar(20),
         firstName varchar(20),
         lastName varchar(20)
 );

insert into bkPhone_template values('0783313780','Brady','Kelly');
 insert into bkPhone_template values('0845319792','Mark','Smith');
 insert into bkPhone_template values('0834976958','Bill','Jones');
 insert into bkPhone_template values('0845319792','Mark','Smith');
 insert into bkPhone_template values('0828329792','Mickey','Mouse');
 insert into bkPhone_template values('0834976958','Bill','Jones');

drop table if exists bkPhone;
create table bkPhone like bkPhone_template;
alter table bkPhone add unique (phoneNo);

insert  ignore into bkPhone (phoneNo,firstName,lastName) select phoneNo,firstName,lastName from bkPhone_template;

drop table bkPhone_template;

If the data table already exists, then you only have to run a create table select with a following insert ignore select. At the end you have to run some table renaming statements. That's all.

如果数据表已经存在，那么您只需要使用下面的insert ignore select来运行create table select。最后，您必须运行一些表重命名语句。这是所有。

This workaround is much,much faster then a delete operation.

这种变通方法比删除操作要快得多。

#2

You can select out the unique ones by:

你可透过以下途径选择最独特的

select distinct(phoneNo) from bkPhone

and put them into another table, delete the old table and rename the new one to the old name.

然后将它们放到另一个表中，删除旧表并将新表重命名为旧表。

#3

MySQL complains, because it makes no sense. You trying to aggregate using min() column by which you group.

MySQL抱怨，因为它毫无意义。您试图使用您分组的min()列进行聚合。

Now, if you're trying to delete duplicate phone numbers for the same person, the SQL should be:

现在，如果要删除同一个人的重复电话号码，SQL应该是:

delete from bkPhone
 where phoneId not in
 (
         select min(phoneId)
         from bkPhone
         group by firstName,lastName /* i.e. grouping by person and NOT grouping by phoneId */
         having  count(*) >= 1
 )

#4

Mysql also included:

Mysql也包括:

http://mssql-to-postgresql.blogspot.com/2007/12/deleting-duplicates-in-postgresql-ms.html

#1