Write a query to to delete duplicate rows?
Answers were Sorted based on User's Feedback
Answer / vipul dalwala
For emample we have table called DUPtable.
DESCRIBE DUPtable;
+--------+-----------------+
| Field | Type |
+--------+-----------------+
| id | Auto Increament |
| field1 | varchar(20) |
| field2 | varchar(20) |
| field3 | varchar(20) |
+--------+-----------------+
And we want to delete duplicate rows from DUPtable (Same
combination of field1, field2 and field3) .
Solution:
Step 1: Create a Temporary table;
CREATE TEMPORARY TABLE tmpDUPtable
SELECT id FROM DUPtable GROUP BY field1, field2, field3;
Step 2: Delete query to remove Rows not in 'tmpDUPtable'
table.
DELETE from DUPtable WHERE id NOT IN (SELECT id FROM
tmpDUPtable);
Step 3 DROP tmpDUPtable
DROP TABLE tmpDUPtable;
I hope this will help.
Is This Answer Correct ? | 3 Yes | 0 No |
Answer / prakash.matte
If we have table 'temp' with columns: id(primary key),
field1, field2, field3, to remove the duplicate (field1,
field2, field3) data we can use either of the below queries
1. delete t1.* from temp t1, temp t2
where t1.id>t2.id and t1.field1=t2.field1 and
t1.field2=t2.field2 and t1.field3=t2.field3;
2. alter ignore table temp ADD UNIQUE INDEX
duprows(field1,field2,field3);
> here we can remove the duprows index by using the
following query:
alter table temp drop index duprows;
we can apply index on the columns as we don't require the
duplicate data, so we don't need to execute the query to
delete the index
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / rakesh dongarwar
$query="SELECT * FROM `emails` ORDER BY `email` ASC LIMIT 0, 30";
mysql_query($query);
$result=mysql_query($query);
$numrows=mysql_num_rows($result);
$totalemailsdeleted=0;
$i=1;
$i2=0;
while($i2 < $numrows)
{
$id=mysql_result($result, $i2, id);
$email=mysql_result($result, $i2, email);
while($i < $numrows)
{
$idcompare=mysql_result($result, $i, id);
$emailcompare=mysql_result($result, $i, email);
if($emailcompare == $email)
{
$query="DELETE FROM `emails` WHERE `id` = '$idcompare' LIMIT 1";
mysql_query($query);
$emailsdeleted.="$emailcompare<br>";
$totalemailsdeleted++;
$numrows=mysql_num_rows($result);
}
else
{
}
$i++;
}
$i2++;
}
Is This Answer Correct ? | 4 Yes | 11 No |
How many types of array supported in php?
Suppose your zend engine supports the mode <? ?> Then how can you configure your php zend engine to support <?php ?> Mode ?
How do you call a constructor for a parent class?
How can you declare the array in php?
How to strip whitespace (or other characters) from the beginning and end of a string?
What is beforeRender() used?
What is MIME?
How to concatenate two strings together in php?
What is a php object?
Tell me is it possible to remove the html tags from data?
What is a procedure in php?
What is rtrim php?