[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