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 |
Explain what does the unlink() function means?
How can I increase my website session?
The left association operator % is used in PHP for?
What is the meaning of curl , which is used while connecting with pay pal? and how it works?
When should you use a stored procedure?
what is the difference between GET and POST?
21 Answers AdVine, Evon Technologies, GVD Infotech, RedAlkemi, SoftSol,
Tips to optimize the php script..... Suggestion for exception handling in php...
Difference between mysql_connect and mysql_pconnect in php?
Do you know how to enable error reporting in php?
What is api example?
Is json a string php?
What are magic constants in php?