busboy — 2013-02-05T20:54:19-05:00 — #1
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
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.
r937 — 2013-02-05T21:40:08-05:00 — #2
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 — 2013-02-05T22:23:42-05:00 — #3
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 — 2013-02-06T00:56:35-05:00 — #4
well, go ahead and do it, then
but test it out on separate test tables first, okay?