[thelist] simple sql question

Joe Ngo chilijoe at gmail.com
Fri Nov 24 23:09:13 CST 2006

On 11/24/06, Brian Cummiskey <brian at hondaswap.com> wrote:
> how do i re-write this as a DELETE statement instead of a SELECT?  I'm
> pruing out some duplicate files...
> SELECT max(fileid) FROM files
> GROUP BY title HAVING count(*) >1
What version of MySQL are you using? Earlier versions does not support
subqueries, in which case the following will not work:

Where fileid NOT IN
(SELECT min(fileid) FROM files
GROUP BY title HAVING count(*) >1)

Since you're trying to remove duplicates,
the above deletion retains the min(fileid) of the same title. You
don't have to iterate more than once when there are multiple
duplicates for a title.

There's still no place like ~

More information about the thelist mailing list