Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Location
    Kalamazoo
    Posts
    42

    Unanswered: Deleting Duplicate Records

    Hi,
    I have a table which has some columns which have repititive values. I want to keep the first value(record) of column(which has the repitive values) and then delete the other records which repeat. Please let me know.
    Thanks.
    Example:
    Table:
    column 1 Column2 Column3 Column4
    1 10 23 15
    2 12 26 14
    3 13 25 14
    4 100 250 14
    I want to delete records number 3-4 but retain the 2nd record.

  2. #2
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606

  3. #3
    Join Date
    Dec 2002
    Posts
    104

    Re: Deleting Duplicate Records

    Originally posted by dba1977
    Hi,
    I have a table which has some columns which have repititive values. I want to keep the first value(record) of column(which has the repitive values) and then delete the other records which repeat. Please let me know.
    Thanks.
    Example:
    Table:
    column 1 Column2 Column3 Column4
    1 10 23 15
    2 12 26 14
    3 13 25 14
    4 100 250 14
    I want to delete records number 3-4 but retain the 2nd record.
    hi!

    just a try....

    assumption: column1 is unique.

    delete try
    from try T1
    where T1.column1 ! = (select min (column1) from try T2 where T1.column4 = T2.column4)

    ---Pooja

  4. #4
    Join Date
    Sep 2003
    Location
    Kalamazoo
    Posts
    42

    Re: Deleting Duplicate Records

    Originally posted by pooja
    hi!

    just a try....

    assumption: column1 is unique.

    delete try
    from try T1
    where T1.column1 ! = (select min (column1) from try T2 where T1.column4 = T2.column4)

    ---Pooja
    Hi Pooja,
    I tried the following SQL commands for deleting the duplicate records:
    DELETE from "LogSchema"."_rt764" T1
    where exists T1.timestamp NOT in (select min(T2.timestamp) from dbo."LogSchema"."_rt764" T2
    where T2."IN48 Result"=T1."IN48 Result" AND T1."SD96 SWinDemand" is NULL)
    ***************
    Delete from "LogSchema"."_rt764" T1 where exists
    (select * from "LogSchema"."_rt764" T2 where T1."IN48 Result"=T2."IN48 Result"
    AND T1."IN48 Result"<T2."IN48 Result" AND T1."SD96 SWinDemand" is NULL)
    When I tried the first SQL with a "Select *" instead of "DELETE", it works..but its the problem with Delete that I am facing..When I tried the first SQL I got an error message which said "syntax error near where"..I am using Sybase, but I think that should not be a problem. Please let me know!
    Thanks.

  5. #5
    Join Date
    Dec 2002
    Posts
    104

    Re: Deleting Duplicate Records

    Hi!

    try using this ...if u want to use alias

    DELETE "LogSchema"."_rt764"
    from "LogSchema"."_rt764" T1
    where exists T1.timestamp NOT in (select min(T2.timestamp) from dbo."LogSchema"."_rt764" T2
    where T2."IN48 Result"=T1."IN48 Result" AND T1."SD96 SWinDemand" is NULL)

    I think this is the problem..........

    ---Pooja

    Originally posted by dba1977
    Hi Pooja,
    I tried the following SQL commands for deleting the duplicate records:
    DELETE from "LogSchema"."_rt764" T1
    where exists T1.timestamp NOT in (select min(T2.timestamp) from dbo."LogSchema"."_rt764" T2
    where T2."IN48 Result"=T1."IN48 Result" AND T1."SD96 SWinDemand" is NULL)
    ***************
    Delete from "LogSchema"."_rt764" T1 where exists
    (select * from "LogSchema"."_rt764" T2 where T1."IN48 Result"=T2."IN48 Result"
    AND T1."IN48 Result"<T2."IN48 Result" AND T1."SD96 SWinDemand" is NULL)
    When I tried the first SQL with a "Select *" instead of "DELETE", it works..but its the problem with Delete that I am facing..When I tried the first SQL I got an error message which said "syntax error near where"..I am using Sybase, but I think that should not be a problem. Please let me know!
    Thanks.

  6. #6
    Join Date
    Sep 2003
    Location
    Kalamazoo
    Posts
    42

    Re: Deleting Duplicate Records

    Originally posted by pooja
    Hi!

    try using this ...if u want to use alias

    DELETE "LogSchema"."_rt764"
    from "LogSchema"."_rt764" T1
    where exists T1.timestamp NOT in (select min(T2.timestamp) from dbo."LogSchema"."_rt764" T2
    where T2."IN48 Result"=T1."IN48 Result" AND T1."SD96 SWinDemand" is NULL)

    I think this is the problem..........

    ---Pooja
    Hi Pooja,
    Its working...Thank you!!!!!

Posting Permissions

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