[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:

DELETE FROM files
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.



-- 
http://xtrahot.chili-mango.net
There's still no place like ~



More information about the thelist mailing list