Page 1 of 3 123 LastLast
Results 1 to 15 of 45
  1. #1
    Join Date
    Jul 2006
    Posts
    17

    Unanswered: Need to remove all duplicate records.

    Hi

    I have a data in one table like below.


    EDITION PRODUCT INSERTDATE
    ---------- ------------ ----------------------
    CNE TN-Town News 12/19/2007 12:00:00 AM
    TN TN-Town News 12/19/2007 12:00:00 AM

    What i have to do is if there are multiple records for one product in any day, then i need to remove all those records. In this case i am getting two records for the PRODUCT 'TN-Town News' and for INSERTDATE = 12/19/2007 . So i need to remove these two records from the table.

    How to do that?. Can anybody help me?

    Thanks
    Venkat

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    step 1 - make a backup
    step 2 - try this:
    Code:
    delete
      from onetable
     where PRODUCT in
    ( select PRODUCT from onetable 
         group by PRODUCT,INSERTDATE 
            having count(*) > 1 )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2006
    Posts
    17

    Need to remove all duplicate records

    Hi

    Thanks for response. But his query is not giving correct data.

    First i need to check if there are multiple EDITIONS for each day, then i need to remove those records.

    Ex: Edition Product Insertdate
    ENN CNN-Garfield 02/12/2008
    WDG CNN-Garfield 02/12/2008
    AWR CNN-Garfield 02/12/2008

    In the above case, i need to remove all records because i found multiple EDITIONS for insertdate 02/12/2008.


    Another example

    Edition Product Insertdate
    ENN CNN-Garfield 02/12/2008
    ENN CNN-Garfield 02/12/2008
    ENN CNN-Garfield 02/12/2008

    In this scenario, i don't need to delete any records because i found only one EDITION for insertdate 02/12/2008.


    Please help me out

  4. #4
    Join Date
    Jul 2006
    Posts
    17

    Problem in removing duplicates

    Hi

    I have a problem in removing duplicate data. Please see below

    Criteria: First i need to check if there are multiple EDITIONS for each day, then i need to remove those records.

    Ex: Edition Product Insertdate
    ENN CNN-Garfield 02/12/2008
    WDG CNN-Garfield 02/12/2008
    AWR CNN-Garfield 02/12/2008

    In the above case, i need to remove all records because i found multiple EDITIONS for insertdate 02/12/2008.


    Another example

    Edition Product Insertdate
    ENN CNN-Garfield 02/12/2008
    ENN CNN-Garfield 02/12/2008
    ENN CNN-Garfield 02/12/2008

    In this scenario, i don't need to delete any records because i found only one EDITION for insertdate 02/12/2008.


    Please help me out

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Threads merged - veparala please just one thread per problem.

    Thanks.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    delete
      from onetable
     where EXISTS 
    ( select 'cmon_rudeboy' from onetable as counter
         where counter.PRODUCT = onetable.PRODUCT and counter.INSERTDATE = onetable.INSERTDATE 
            having count(*) > 1 )

  7. #7
    Join Date
    Jul 2006
    Posts
    17
    When i execute your query. it is giving the following error message.

    [Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 'ANEDITORIALINSERTTEST' does not match with a table name or alias name used in the query. 

    What should i do?

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Please post your SQL.

  9. #9
    Join Date
    Jul 2006
    Posts
    17
    Hi

    when i execute your query. It has deleted all records from the table.

    Please see below my query

    delete
    from ANEDITORIALINSERTTEST
    where EXISTS (
    select * from
    ANEDITORIALINSERTTEST as counter
    where
    counter.PRODUCT = ANEDITORIALINSERTTEST.PRODUCT
    and counter.INSERTDATE = ANEDITORIALINSERTTEST.INSERTDATE
    having count(*) > 1)

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    So it does not error now?
    You had no records that were not duplicated?

  11. #11
    Join Date
    Jul 2006
    Posts
    17
    It is not giving any error now. But query has been deleted all records, even distinct records.

    Edition Product Insertdate
    ENN CNN-Garfield 02/12/2008
    ENN CNN-Garfield 02/12/2008
    ENN CNN-Garfield 02/12/2008


    If you take above examples, query is deleting all records. But i don't want delete the above records because there are no multiple Editions for insertdate 02/12/2008 and product 'CNN-Garfield '. There is only one edition, i.e ENN

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You didn't specify that originally.
    Quote Originally Posted by veparala
    What i have to do is if there are multiple records for one product in any day, then i need to remove all those records. In this case i am getting two records for the PRODUCT 'TN-Town News' and for INSERTDATE = 12/19/2007 . So i need to remove these two records from the table.
    Reload your table and try:
    Code:
     delete
      from ANEDITORIALINSERTTEST
     where EXISTS (
    select * from 
    ANEDITORIALINSERTTEST as counter
    where 
    counter.PRODUCT = ANEDITORIALINSERTTEST.PRODUCT 
    and counter.INSERTDATE = ANEDITORIALINSERTTEST.INSERTDATE 
    having count(DISTINCT Edition) > 1)

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    delete
      from ANEDITORIALINSERTTEST 
     where PRODUCT in
    ( select PRODUCT from ANEDITORIALINSERTTEST 
         group by PRODUCT,INSERTDATE 
            having count(*) > 1 )
    after restoring your backup, please try that again

    if it's not right, i wonder if you would kindly print the first few lines of the output produced by the subquery
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Rudy - isn't that incorrect given the OPs specification (clarified in post 11)?

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump
    Rudy - isn't that incorrect ...
    aw shit

    yes it is

    never mind any givens, it was wrong from the get go

    "i am such an idiot"

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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