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 soundex() and metaphone().
How to remove leading and trailing spaces from user input values in php?
what is the difference between ph4 and php5
How do I find out the number of parameters passed into function9?
Tell me what is the use of the function htmlentities?
What is ci in php?
Which is incorrect with respect to separating php code and html?
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?
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
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?
how can we retrive value from one database server and store them another database server using php?