Database deleting rows with same value

Question

Duplicate of How to delete duplicate records in mysql database?

   id  |  name   |      link
    1     bla     www.google.com
    2     lal     www.stackoverflow.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.

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

may you get solution here http://stackoverflow.com/questions/659906/how-to-delete-duplicate-records-in-mysql-database — Sonal Khunt, Dec 03 '11 at 11:14

Jan Vorcak · Accepted Answer · 2011-12-03T11:28:09.527

trick is to keep latest value with comparing ids

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)

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

score 1 · Answer 2 · answered Dec 03 '11 at 11:15

If you know the URL, but not the ID, but know you only want to delete one, you could use a limit.

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'

score 0 · Answer 3 · answered Dec 03 '11 at 11:13

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

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.

djot · Answer 4 · 2011-12-03T11:20:42.033

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

DELETE FROM table where id > first_ID AND link like 'www.google.com'

edited Dec 03 '11 at 11:20

answered Dec 03 '11 at 11:14

djot

2,952
4
19
28

score 0 · Answer 5 · answered Dec 03 '11 at 11:26

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] 
";
}

score 0 · Answer 6 · answered Dec 03 '11 at 11:28

Ok I've set this up as a seperate answer and deleted my other one

First, Select all the Distinct links in the table

SELECT DISTINCT( link ) FROM table

Then, using PHP, loop over each row returned and apply the SQL below to the links retirved.

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'
                  )
)

Database deleting rows with same value

6 Answers6