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 |
Code to upload a file in PHP?
What are the ways we can destroy a session variable ?
What is encapsulation in php with example?
How to download a php script directly in your script page?
3 Answers A1 Technology, Zmanda,
Is it possible to destroy a cookie?
Explain what is the difference between for and foreach?
How can we do user authentication without using session/cookies ? For ex:- From page1 accepts user name and password and need to check in all other pages whether the user has logged in or not
What are the method available in form submitting?
Define anonymous classes in php7?
What is the difference between for and foreach in php?
Which variable declarations within a class is invalid in php?
How cookies are trported from browsers to servers?