Results 1 to 2 of 2

Thread: can we somehow mark duplicate rows ?

  1. #1
    Nisha Guest

    can we somehow mark duplicate rows ?

    Hi,
    I am encountering a problem. There are lots of duplicate rows in the cobol flat files (due to improper data entry and missing columns values )from where I am transforming data to sql 7. 0 tables using DTS. After transformation , can I some how mark the duplicate rows ? it is not for the purpose of eliminating them, but to enter the missing values and make all the rows complete and unique.
    I have the transformed table as a temporary table. Can I add a column like 'status' etc.. and have the column values marked '1' for the repeating rows etc....
    Can anyone suggest 'any' possible way of implementing it ?
    Thanx
    Nisha

  2. #2
    Duncan Maddox Guest

    can we somehow mark duplicate rows ? (reply)


    Hi !

    A simple example of marking dupes is as follows,
    assuming that you have added a NULL-able char(1) field called dupe and "duplicates" are where columns one,two,three are the same ...

    update [table]
    set dupe = "Y"
    from [table] a
    where
    (
    select count(*)
    from [table] b
    where b.one=a.one and b.two=a.two and b.three=a.three
    )
    >1

    This is a correlated subquery, so it wont be fast, but it should work !

    Duncan

    ------------
    Nisha at 9/14/99 1:57:13 AM

    Hi,
    I am encountering a problem. There are lots of duplicate rows in the cobol flat files (due to improper data entry and missing columns values )from where I am transforming data to sql 7. 0 tables using DTS. After transformation , can I some how mark the duplicate rows ? it is not for the purpose of eliminating them, but to enter the missing values and make all the rows complete and unique.
    I have the transformed table as a temporary table. Can I add a column like 'status' etc.. and have the column values marked '1' for the repeating rows etc....
    Can anyone suggest 'any' possible way of implementing it ?
    Thanx
    Nisha

Posting Permissions

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