Deleting rows repeating

[Anon]

I'm trying to delete all rows that are repeating so that only one unique row of that specific data remains.

Right now, I'm doing this by creating a temporary table from the result set of the column of data repeating as a GROUP:

CREATE TABLE files_temp AS SELECT * FROM files GROUP BY file;

Then I drop the old table:

DROP TABLE tella_files;

Then recreate a new table with the new data:

CREATE TABLE files AS SELECT * FROM files_temp;

Then drop the old temporary table:

DROP TABLE files_temp;

Is there a way I can do all of this with just one query. I tried using DISTINCT, but can't get it working like I wanted.

[stallion]

Nope, there's really no other way to do this -- except by using a SELECT, and then a really big DELETE.

<?
unset($temp);
$rows = mysql_query("SELECT id FROM files WHERE COUNT(*) > 1 AND MAX(id) <> id GROUP BY file");
while ($row = mysql_fetch_array($rows) {
if ($temp)
$temp .= ", ";
$temp = $row[id];
}
mysql_query("DELETE FROM files WHERE id IN ($temp)");
?>

Btw, how's HotScripts holding up without our beloved forums? 🙂

[stallion]

slaps himself 🙂

HotScripts = Jon Yoon
BlazeBoard = Jon Chum

[shanx24]

Hi Jonathan,

1. I might be mistaken but MySQL wont allow you to have grouping functions like count() in your where clause (at least the 3.23.28 version that I am using - I dont know if this has been fixed later). But then you might be using some other database?

2. The logic that Stallion presented is correct and you might try to use the same for your delete as well.

DELETE FROM TABLE WHERE COUNT(COLUMN) > 1 GROUP BY COLUMN;

[Anon]

Ha! 🙂 Let me slap you once for that! 🙂

[[deleted]]

DELETE FROM TABLE WHERE COUNT(COLUMN) > 1 GROUP BY COLUMN;

If that would work it would delete all records that appear more than once completely, not just the second, third etc.

[Anon]

Here's some ideas that might help:

Would it be possible to eliminate the insertion of the duplicates by creating a unique index and then ignoring/suppressing the "duplicate insert" errors coming from the server?

---

Could you add an AutoIncrement field to the table to create a unique key and then delete records based on that key? The following works in SQL Server (but only because it supports sub selects). RecordId is the AutoIncrement number:

Delete From TestTable
Where RecordId Not In
(Select min(RecordId)
From TestTable
Group by FieldX)

---

Could you add an AutoIncrement field as a primary key and then open an update cursor on the table:

Select file, Id from table order by file
while !eof
if tempfile <> file then
tempfile = file
else
delete record

In this scenario, each time the field changes, you save it to a temp variable. Each fetch that follows that has the same value, delete it. (FYI - Delete cursors only work on tables that have a primary key.)

I'm sure there are other solutions and some of these might not work for your paticular situation. But maybe this will give you some ideas...

[Anon]

Thanks for the tips. I, for some reason forgot the obvious, placing the column to be unique. 🙂

[stallion]

...which is why you'd add MAX(id) <> id

But you can't GROUP in DELETEs, so that wouldn't work anyways.

[stallion]

If you can't use COUNT() in WHERE, then just use:
SELECT , COUNT() AS cnt FROM files WHERE cnt > 1 ...

I believe you can't use GROUP with DELETE, though.

[shanx24]

Still doesnt work with MySQL, Stallion..

-> SELECT
->  *
->  ,COUNT(*) AS cnt
-> FROM
->  track_detail
-> WHERE
->  cnt > 1
-> ;

ERROR 1054: Unknown column 'cnt' in 'where clause'

Any suggestions?

[[deleted]]

I know, just clarifying...

[[deleted]]

You'd have to use the HAVING clause, that works:
-> SELECT
->
-> ,COUNT() AS cnt
-> FROM
-> track_detail
-> GROUP BY whatever
-> HAVING cnt>1
-> ;