Write a query to to delete duplicate rows?

Answers were Sorted based on User's Feedback



Write a query to to delete duplicate rows?..

Answer / amit

This Question is Wrong.....

Is This Answer Correct ?    9 Yes 1 No

Write a query to to delete duplicate rows?..

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

Write a query to to delete duplicate rows?..

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

Write a query to to delete duplicate rows?..

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

Post New Answer

More PHP Interview Questions

What is mysql_fetch_array?

0 Answers  


How can we automatically escape incoming data?

0 Answers  


What is a php class?

0 Answers  


How to check a variable is an array or not in php?

0 Answers  


!== represents what?

2 Answers  






Can you use php and javascript together?

0 Answers  


What is the use of namespace in php?

0 Answers  


What is warning – “cannot modify header information – headers already sent”?

0 Answers  


How to apply Cake php ajax pagination?

1 Answers  


What exactly is validating and sanitizing?

0 Answers  


I finished my degree 4 years back. I didnt get job in software. In the time(for 4 years) i worked one kpo industries. but now i got job in php(40 employes working there) as fresher(Salary 8000). Shall i join in php. I have no knowledge about php. Can i do well in php. How will my carrier if i join in php? Please can you give your idea.? It will be more usefull to me.

2 Answers  


What is the major php security hole? How to avoid?

0 Answers  


Categories