Need a confirmation before I run this DELETE query

[busboy]

So I have learned that I haven't been using UNIQUE indexes in my tables like I should have, and now there are many duplicates I must delete. I did my homework on the web and pieced together the following query:

delete from notifications
where dateAdded in (select dateAdded from (
select dateAdded, uID, count(*) cnt
from notifications
where ( uID <> 0
group by dateAdded, uID
having cnt > 1 ) x
) );

Does my syntax look correct? This should, hopefully, delete all duplicate rows that have the same dateAdded and uID.

Thanks!

[r937]

Does my syntax look correct?

no, there are several problems

the syntax errors are not as important as this, though -- you cannot delete from the same table that you're selecting from

you will have to use a second table to hold the identifiers of the rows you want to delete

and of course the way to test the whole thing is to set up two testing tables, and populate them with some representative data, so that you can run your delete and make sure the right rows get deleted before repeating the process on your main table

[busboy]

Weird. I read on some other websites that using this method, with the additional select and the use of x as an alias would "trick" mySQL into letting the query work.

[r937]

Weird. I read on some other websites that using this method, with the additional select and the use of x as an alias would "trick" mySQL into letting the query work.

o rly?

well, go ahead and do it, then

but test it out on separate test tables first, okay?