SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2002
    Posts
    75
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    show repeated row

    I have a table for mailinglist,

    How can show all repeated rows if any ? then to delete them

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    to show them, use this:
    Code:
    select foo, bar, columns 
      from mailinglist
    group by foo, bar, columns
    having count(*)>1
    to remove repeated rows, do these three steps:
    Code:
    create table new1 select distinct foo, bar, columns from mailinglist
    
    drop table mailinglist
    
    rename table new1 to mailinglist
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict mr tinkles's Avatar
    Join Date
    Jan 2003
    Posts
    262
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    just thinking...

    why do you have repeated rows?

    find source of problem!

  4. #4
    SitePoint Enthusiast
    Join Date
    Jun 2002
    Posts
    75
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    to show them, use this:
    Code:
    select foo, bar, columns 
    from mailinglist
    group by foo, bar, columns
    having count(*)>1
    to remove repeated rows, do these three steps:
    Code:
    create table new1 select distinct foo, bar, columns from mailinglist
     
    drop table mailinglist
     
    rename table new1 to mailinglist
    what do you mean by foo, bar, columns ?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    foo is the first on the standard list of metasyntactic variables used in syntax examples

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

  6. #6
    Non-Member hyperimage's Avatar
    Join Date
    Dec 2003
    Location
    Croatia
    Posts
    259
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it is like "kung" fu = foo
    the firts expression

    cya

  7. #7
    SitePoint Addict mr tinkles's Avatar
    Join Date
    Jan 2003
    Posts
    262
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    "what do you mean by foo, bar, columns ?"

    "How can show all repeated rows if any ? then to delete them"

    how do you you know what is repeated?

    how do you determine a repeated row?

  8. #8
    blonde.... Sarah's Avatar
    Join Date
    Jul 2001
    Location
    Berkshire, UK
    Posts
    7,442
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Marwan,

    As you didn't explain the database or table structure you are using Rudy gave you a generic SQL statement to use, now you need to replace "foo, bar, columns" with your table name and column name that you want to use.

    The best way I would suggest is to try and couple of examples and see what you get - you don't have to delete any until you are sure.

    But essentially the asnwer is right there - if you still have trouble, post the table/database structure that you are concerned with and what SQL you tried and why it didn't work and we will come back and help (or rather we -> read Rudy )

    Thanks
    Sarah
    Regular user


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
  •