Find Duplicates 1

[ArtWerk]

Is there an easy way to find duplicate rows (where every field is the same) and then remove all but 1 of the duplicates?

 

[TonyGroves]

If it's just a once-off operation, then you could dump the non-duplicate records to a temporary table, empty the original table, and load the temporary table's records back in:
[tt]
CREATE TEMPORARY TABLE t AS SELECT DISTINCT * FROM tbl;
TRUNCATE tbl;
INSERT tbl SELECT * FROM t;
[/tt]

 

[ArtWerk]

we're pretty close!

I'm getting this error now:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; TRUNCATE `pcp`; INSERT `pcp` SELECT * FROM t' at line 1

This is how i have my query:

"CREATE TEMPORARY TABLE t AS SELECT DISTINCT * FROM `".$list."`;
		TRUNCATE `".$list."`;
		INSERT `".$list."` SELECT * FROM t;"

 

[TonyGroves]

Those are 3 separate queries.

 

[ArtWerk]

oh... i guess i can't do them all at once... i'm stupid..

 

[ArtWerk]

One last question, and this may be something to ask in the PHP forum. I've split this into 3 queries (along with an optimize query) and this is my code:

$query_dedup1 = "CREATE TEMPORARY TABLE t AS SELECT DISTINCT * FROM `".$list."`;";
	$dedup1_res = mysql_query($query_dedup1) or die(mysql_error());
	$query_dedup2 = "TRUNCATE `".$list."`;";
	$dedup2_res = mysql_query($query_dedup2) or die(mysql_error());
	$query_dedup3 = "INSERT `".$list."` SELECT * FROM t;";
	$dedup3_res = mysql_query($query_dedup3) or die(mysql_error());
	
	$query_optimize = "OPTIMIZE TABLE `".$list."`;";
	$optimize_res = mysql_query($query_optimize) or die(mysql_error());

My question is I'd like to get information about these SQL statements, such as how many records were deleted, how many there were and how many there are now, and if possible, which records had duplicates, how many, etc. is all that available with more queries or php functions?

 

[TonyGroves]

To get the number of records then/now/deleted, you could run a COUNT(*) query on each table and subtract the two figures.

To list the duplicates in the original table, you could use something like:[tt]
SELECT COUNT(*) cnt,fld1,fld2,fld3,...
FROM tbl
GROUP BY fld1,fld2,fld3 ...
HAVING cnt>1
[/tt]