SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Need a confirmation before I run this DELETE query

    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!
    Convert your dollars into silver coins. www.convert2silver.com

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by busboy View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.
    Convert your dollars into silver coins. www.convert2silver.com

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by busboy View Post
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •