[thelist] simple sql question

Dave Stevens evolt at davestevens.co.uk
Fri Nov 24 09:36:22 CST 2006


Hi Brian,

I'm sure more knowledgeable list members will be able to advise you of a 
better method, but I would do:

DELETE FROM files WHERE fileid IN (SELECT max(fileid) FROM files GROUP 
BY title HAVING count(*) >1)

Hope this helps.
Dave

Brian Cummiskey 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
> 
> 
> Everytime i try to delete it via phpmyadmin on the X, it gives me the 
> following error:
> 
>   Error
> *SQL query:*
> 
> DELETE FROM `files` WHERE `files`.`max( fileid )` =563 LIMIT 1
> 
> *MySQL said: * 
> | #1054 - Unknown column 'files.max( fileid )' in 'where clause'




More information about the thelist mailing list