Updating Duplicate data in a table to Make it Unique

• schwizla

  SSCrazy

  Points: 2668

  More actions

  May 5, 2009 at 8:52 am

  #155658

  I have a table with 6120 records

  2275 records = Unique LicenseNumber

  1941 records = duplicate x2

  11 records = x3

  5 records = x4

  ProductLicense_Idn (PK, int, not null)

  LicenseNumber (Varchar(255), not null)

  The LicenseNumber needs to be unique and I have to modify only the duplicates entities as such.

  so for example

  ProductLicense_Idn LicenseNumber

  1 SYD12N

  2 toronto

  3 toronto

  4 AER45SF

  5 AER45SF

  6 AER45SF

  ...becomes

  ProductLicense_Idn LicenseNumber

  1 SYD12N

  2 toronto - 1

  3 toronto - 2

  4 AER45SF - 1

  5 AER45SF - 2

  6 AER45SF - 3

  so that I have a table with 6120 unique records

  assistance much appreciated!

  Thanks

  S

• Tobias Kiemes

  SSCommitted

  Points: 1604

  More actions

  May 5, 2009 at 9:03 am

  #988913

  schwizzla (5/5/2009)

  ---

  I have a table with 6120 records

  2275 records = Unique LicenseNumber

  1941 records = duplicate x2

  11 records = x3

  5 records = x4

  ProductLicense_Idn (PK, int, not null)

  LicenseNumber (Varchar(255), not null)

  The LicenseNumber needs to be unique and I have to modify only the duplicates entities as such.

  so for example

  ProductLicense_Idn LicenseNumber

  1 SYD12N

  2 toronto

  3 toronto

  4 AER45SF

  5 AER45SF

  6 AER45SF

  ...becomes

  ProductLicense_Idn LicenseNumber

  1 SYD12N

  2 toronto - 1

  3 toronto - 2

  4 AER45SF - 1

  5 AER45SF - 2

  6 AER45SF - 3

  so that I have a table with 6120 unique records

  assistance much appreciated!

  Thanks

  S

  perhaps like this:

  select license_id

  , LicenseNumber AS old_name

  , LicenseNumber + ' - ' + str(ROW_NUMBER() OVER(ORDER BY LicenseNumber asc)) AS 'new_name'

  from tab1

• schwizla

  SSCrazy

  Points: 2668

  More actions

  May 5, 2009 at 9:11 am

  #988925

  Hi Tobe_ha,

  that works great but I am ending up with the follwing

  ProductLicense_Idn LicenseNumber

  1 SYD12N - 1

  2 toronto - 2

  3 toronto - 3

  4 AER45SF - 5

  5 AER45SF - 5

  6 AER45SF - 6

  How can I restart the numbering?

  e.g

  ProductLicense_Idn LicenseNumber

  1 SYD12N - 1

  2 toronto - 1

  3 toronto - 2

  4 AER45SF - 1

  5 AER45SF - 2

  6 AER45SF - 3

  Thanks

  S

• steveb.

  SSC-Forever

  Points: 46733

  More actions

  May 5, 2009 at 9:22 am

  #988939

  try this code, it should work fine, but you may need to change it slightly if you dont want the single unique rows to be numbered

  UPDATE [YourTable]

  SET [LicenseNumber] = LicenseNumber + '-' + CAST(TheOrder AS CHAR(4))

  FROM (SELECT ProductLicense_Idn, ROW_NUMBER() OVER (PARTITION BY LicenseNumber ORDER BY ProductLicense_Idn) AS TheOrder FROM YourTable)AS sub

  WHERE [YourTable].[ProductLicense_Idn] = sub.ProductLicense_Idn

• schwizla

  SSCrazy

  Points: 2668

  More actions

  May 5, 2009 at 9:33 am

  #988953

  works a treat 🙂

  thanks for your help...