SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Thread: SQL Question.

Hybrid View

  1. #1
    SitePoint Addict
    Join Date
    Feb 2002
    Location
    Oklahoma
    Posts
    228
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Question.

    I have a table in my database that I use to store userid and propertyid values. I allow users to save properties they found in our public property search site. The problem is that I did not apply the constraint that would have kept them from saving the same property twice. So user 2234 could save property 45678 more than once. I can not apply the constrain now because it has already been violated. I need to clear all duplicate saves from the table. The table column structure is Userid, mlsnum, dateinserted. The unque constrain should have been applied to the userid and mlsnum columns. I have created a query to show me the offending records:
    Code:
    Select UserId, mlsnum
    from saved_property
    order by uerid, mlsnum
    compute count(mlsnum) by userid, mlsnum
    This shows records like so:

    Userid MLSNum
    700 124069
    Cnt
    1

    This shows me how many times a user saved any given property. The problem is that htis query kicks the crap out of my p-4, 512mb pc. It bombs out after showing a few thousend records. I need to restrict the records to only the people that have a cnt of 2 or more. Any ideas???

  2. #2
    Wanna-be Apple nut silver trophy M. Johansson's Avatar
    Join Date
    Sep 2000
    Location
    Halmstad, Sweden
    Posts
    7,400
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Moved to database forum.
    Mattias Johansson
    Short, Swedish, Web Developer

    Buttons and Dog Tags with your custom design:
    FatStatement.com

  3. #3
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,294
    Mentioned
    123 Post(s)
    Tagged
    1 Thread(s)
    Code:
    SELECT UserId, mlsnum, count(*) AS NumRecs
      FROM saved_property
     GROUP BY UserID, mlsNum
     ORDER BY UserID, mlsNum
    HAVING Count(*) > 1
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  4. #4
    SitePoint Addict
    Join Date
    Feb 2002
    Location
    Oklahoma
    Posts
    228
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK. So I have 1085 duplicate records. What would be the best way to get rid of the duplicate with out deleting both records?

    My table structure is:
    SavedID (Incremental 1,1)
    Userid Int
    MLSNum Int
    DateInserted DateTime

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    delete
    from saved_property A
    inner
    join ( select UserId, mlsnum
    , min(dateinserted) as lowdate
    from saved_property
    group by UserId, mlsnum
    having count(*) > 1 ) D
    on A.UserId = D.UserId
    and A.mlsnum = D.mlsnum
    where A.dateinserted > D.lowdate

    caution: this is untested, so back up your table first

    rudy
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Addict
    Join Date
    Feb 2002
    Location
    Oklahoma
    Posts
    228
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    delete
    from saved_property A
    inner
    join ( select UserId, mlsnum
    , min(dateinserted) as lowdate
    from saved_property
    group by UserId, mlsnum
    having count(*) > 1 ) D
    on A.UserId = D.UserId
    and A.mlsnum = D.mlsnum
    where A.dateinserted > D.lowdate

    caution: this is untested, so back up your table first

    rudy
    Thanks I will give it a try. What is backing up?

  7. #7
    SitePoint Addict
    Join Date
    Feb 2002
    Location
    Oklahoma
    Posts
    228
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    All is well. Thanks everyone.


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
  •