Duplicate of How to delete duplicate records in mysql database?
重复如何删除mysql数据库中的重复记录?
id | name | link
1 bla www.google.com
2 lal www.*.com
3 afs www.google.com
4 sds www.microsoft.com
In my database there some rows with same link. I want to delete one of that rows with the same link.
在我的数据库中有一些行具有相同的链接。我想删除其中一个具有相同链接的行。
Such as, in my example I want to delete the row with the id = 3 but I want to keep id = 1.
比如,在我的例子中我想删除id = 3的行,但我想保持id = 1。
How can I do this?
我怎样才能做到这一点?
by the way there are 5840 rows. and I don't know which are the same. I need a code to compare them
顺便说一下有5840行。我不知道哪一个是一样的。我需要一个代码来比较它们
6 个解决方案
#1
3
trick is to keep latest value with comparing ids
诀窍是通过比较ID来保持最新价值
DELETE FROM table_name t1, table_name t2 WHERE t1.name = t2.name and t1.link = t2.link and t1.id < t2.id
It's just and idea, this is a fully functional example on my oracle environment (I haven't use db few weeks, so I'm trying to minimalize the query right now, sorry)
这只是一个想法,这是我的oracle环境中一个功能齐全的例子(我没有使用db几周,所以我现在正在尝试最小化查询,抱歉)
SQL> select * from dup;
ID STH STH2
---------- ---------- ----------
1 45 45
2 45 56
3 45 45
4 14 56
5 14 56
DELETE FROM dup where id in (SELECT DISTINCT t1.id FROM dup t1, dup t2 WHERE t1.sth = t2.sth and t1.sth2 = t2.sth2 and t1.id < t2.id);
SQL> select * from dup;
ID STH STH2
---------- ---------- ----------
2 45 56
3 45 45
5 14 56
#2
0
well, for SQL you'll have to specify which one to keep, usually. Settle for example for the first id
. Then you can do a
好吧,对于SQL,你必须指定通常保留哪一个。以第一个id为例。然后你可以做一个
select min(id), link from TABLE group by link
and then delete all those where the link
doesn't match the minimum id. Should be a single (albeit nested) SQL statement.
然后删除链接与最小ID不匹配的所有内容。应该是单个(尽管是嵌套的)SQL语句。
#3
0
If you know the URL, but not the ID, but know you only want to delete one, you could use a limit.
如果您知道URL,但不知道ID,但知道您只想删除一个,则可以使用限制。
DELETE FROM `table` WHERE `link` = 'www.google.com' LIMIT 1
Or if you know the name and link, then use them together.
或者,如果您知道名称和链接,则将它们一起使用。
DELETE FROM `table` WHERE `name` = 'afs' AND `link` = 'www.google.com'
#4
0
You could select the first occurence of e.g. google.com, then get the ID (as e.g. first_ID) of the first google.com entry and delete everything that came after this id
您可以选择第一次出现,例如google.com,然后获取第一个google.com条目的ID(例如first_ID)并删除此ID之后的所有内容
DELETE FROM table where id > first_ID AND link like 'www.google.com'
从表中删除id> first_ID和'www.google.com'之类的链接
#5
0
Not tested but I think this is gonna work
没有经过测试,但我认为这会起作用
$query="SELECT * FROM tablename";
$result=mysql_query($query);
while($row = mysql_fetch_array($result) { $query1="SELECT * FROM tablename WHERE link = '".$row[2]."'"; $result1=mysql_query($query1); $count = mysql_num_rows($result1); mysql_query("DELETE FROM tablename WHERE link='".$row[2]."' LIMIT 1,$count"); echo "deleted $row[1]
"; }
而($行= mysql_fetch_array($结果){$ QUERY1 = “SELECT * FROM表名WHERE链接= '” $行[2]。 “'”; $ RESULT1 =的mysql_query($ QUERY1); $计数= mysql_num_rows($ RESULT1);请求mysql_query( “DELETE FROM表名WHERE链接= '” $行[2]。 “' LIMIT 1,$计数”);回声“删除$行[1]”;}
#6
0
Ok I've set this up as a seperate answer and deleted my other one
好的,我把它设置为一个单独的答案,并删除了我的另一个
First, Select all the Distinct links in the table
首先,选择表中的所有Distinct链接
SELECT DISTINCT( link ) FROM table
Then, using PHP, loop over each row returned and apply the SQL below to the links retirved.
然后,使用PHP循环返回的每一行,并将下面的SQL应用于已提交的链接。
DELETE FROM table
WHERE id IN (
//Selects Ids whose value is more than the 1st occurence
SELECT id
FROM table
WHERE
link = 'CURRENT_LINK'
AND id > ( // Check this isn't the first occurence
SELECT MIN( id )
FROM table
WHERE link = 'CURRENT_LINK'
)
)
#1
3
trick is to keep latest value with comparing ids
诀窍是通过比较ID来保持最新价值
DELETE FROM table_name t1, table_name t2 WHERE t1.name = t2.name and t1.link = t2.link and t1.id < t2.id
It's just and idea, this is a fully functional example on my oracle environment (I haven't use db few weeks, so I'm trying to minimalize the query right now, sorry)
这只是一个想法,这是我的oracle环境中一个功能齐全的例子(我没有使用db几周,所以我现在正在尝试最小化查询,抱歉)
SQL> select * from dup;
ID STH STH2
---------- ---------- ----------
1 45 45
2 45 56
3 45 45
4 14 56
5 14 56
DELETE FROM dup where id in (SELECT DISTINCT t1.id FROM dup t1, dup t2 WHERE t1.sth = t2.sth and t1.sth2 = t2.sth2 and t1.id < t2.id);
SQL> select * from dup;
ID STH STH2
---------- ---------- ----------
2 45 56
3 45 45
5 14 56
#2
0
well, for SQL you'll have to specify which one to keep, usually. Settle for example for the first id
. Then you can do a
好吧,对于SQL,你必须指定通常保留哪一个。以第一个id为例。然后你可以做一个
select min(id), link from TABLE group by link
and then delete all those where the link
doesn't match the minimum id. Should be a single (albeit nested) SQL statement.
然后删除链接与最小ID不匹配的所有内容。应该是单个(尽管是嵌套的)SQL语句。
#3
0
If you know the URL, but not the ID, but know you only want to delete one, you could use a limit.
如果您知道URL,但不知道ID,但知道您只想删除一个,则可以使用限制。
DELETE FROM `table` WHERE `link` = 'www.google.com' LIMIT 1
Or if you know the name and link, then use them together.
或者,如果您知道名称和链接,则将它们一起使用。
DELETE FROM `table` WHERE `name` = 'afs' AND `link` = 'www.google.com'
#4
0
You could select the first occurence of e.g. google.com, then get the ID (as e.g. first_ID) of the first google.com entry and delete everything that came after this id
您可以选择第一次出现,例如google.com,然后获取第一个google.com条目的ID(例如first_ID)并删除此ID之后的所有内容
DELETE FROM table where id > first_ID AND link like 'www.google.com'
从表中删除id> first_ID和'www.google.com'之类的链接
#5
0
Not tested but I think this is gonna work
没有经过测试,但我认为这会起作用
$query="SELECT * FROM tablename";
$result=mysql_query($query);
while($row = mysql_fetch_array($result) { $query1="SELECT * FROM tablename WHERE link = '".$row[2]."'"; $result1=mysql_query($query1); $count = mysql_num_rows($result1); mysql_query("DELETE FROM tablename WHERE link='".$row[2]."' LIMIT 1,$count"); echo "deleted $row[1]
"; }
而($行= mysql_fetch_array($结果){$ QUERY1 = “SELECT * FROM表名WHERE链接= '” $行[2]。 “'”; $ RESULT1 =的mysql_query($ QUERY1); $计数= mysql_num_rows($ RESULT1);请求mysql_query( “DELETE FROM表名WHERE链接= '” $行[2]。 “' LIMIT 1,$计数”);回声“删除$行[1]”;}
#6
0
Ok I've set this up as a seperate answer and deleted my other one
好的,我把它设置为一个单独的答案,并删除了我的另一个
First, Select all the Distinct links in the table
首先,选择表中的所有Distinct链接
SELECT DISTINCT( link ) FROM table
Then, using PHP, loop over each row returned and apply the SQL below to the links retirved.
然后,使用PHP循环返回的每一行,并将下面的SQL应用于已提交的链接。
DELETE FROM table
WHERE id IN (
//Selects Ids whose value is more than the 1st occurence
SELECT id
FROM table
WHERE
link = 'CURRENT_LINK'
AND id > ( // Check this isn't the first occurence
SELECT MIN( id )
FROM table
WHERE link = 'CURRENT_LINK'
)
)