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

Explain soundex() and metaphone().

0 Answers  


How to remove leading and trailing spaces from user input values in php?

0 Answers  


what is the difference between ph4 and php5

2 Answers  


How do I find out the number of parameters passed into function9?

0 Answers  


Tell me what is the use of the function htmlentities?

0 Answers  


What is ci in php?

0 Answers  


Which is incorrect with respect to separating php code and html?

0 Answers  


How can we know that a session is started or not?

3 Answers   Rushmore Consultancy,


what is constructor in a class, how it is work, how it is call?

2 Answers  


i want to need upload above 100mb file,i was successfully upload 75mb using .htaccess file ,but i can put 101mb file,it was not upload..please give some tips..local server its work perfectly but online it doesn't work.. .htaccess file details AddHandler application/x-httpd-php .php php_value post_max_size 200M php_value upload_max_filesize 200M php_value memory_limit 300M php_value output_buffering on php_value max_execution_time 259200 php_value max_input_time 259200 php_value session.cookie_lifetime 0 php_value session.gc_maxlifetime 259200 php_value default_socket_timeout 259200

1 Answers  


if i give Limit for mysql query through php command line script like this for ex. $limit=500; $total_items = 1500; for($start=0;$start<$total_items;){ $command = "/usr/bin/php -q /home/sitename/public_html/admin/feedmanager/test.php feedid ".$_GET['feedid']." start ".$start." end ".$limit ; $start = $start+$limit; $command_result = system($command); } so it's execute command for 3 times so it give limit to sql query but if i have 27000 data so it takes long time? so my question is that is that any way from php command line script that i can use for collect all the data at once?

0 Answers  


how can we retrive value from one database server and store them another database server using php?

3 Answers  


Categories