[thelist] simple sql question

Phil Turmel pturmel-webdev at turmel.org
Fri Nov 24 19:17:35 CST 2006


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

Try something like this:

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

Note that you need a sub-query capable db to do this.  Otherwise you'll
have to script it in two operations...

Also, I left out the "LIMIT 1" clause, as I'm not exactly sure what you
wanted it to accomplish.  Add it back if your testing suggests you need
it. (You are going to test this, right?)

HTH,

Phil



More information about the thelist mailing list