Duplicates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Duplicates

Author

Topic

syed498
Starting Member

5 Posts

Posted - 2014-02-28 : 10:09:59

Hi Team,

I am a basic sql server 2005 user.

I have a small query. My query is that I have a Table (Table1)which have customerno, f_name,L_Name as column name .

Table1:
CustomerNo F_Name L_Name
ABC_001
ABC_001_DUP2
ABC_002
ABC_003
ABC_003_DUP2

I have another table Table2, which has same column names as above and customerNo column has below:

CustomerNo F_Name L_Name
ABC_001
ABC_002
ABC_004

Now I want to update my Table1 with Table2 values, but instead of overwriting the duplicates I want to append the value with _DUP and increment the number with the number of times that value occurs.

For the above Example.

My final Table1 should look like this below:

CustomerNo F_Name L_Name
ABC_001
ABC_001_DUP2
ABC_002
ABC_003
ABC_003_DUP2
ABC_001_DUP3
ABC_002_DUP2
ABC_004

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-02-28 : 11:23:14

[code]UPDATE f
SET f.CustomerNo = CASE WHEN rn = 1 THEN f.CustomerNo ELSE f.CustomerNo + '_DUP' + CAST(rn AS VARCHAR(12)) END
FROM (
SELECT CustomerNo,
ROW_NUMBER() OVER (PARTITION BY CustomerNo ORDER BY CustomerNo) AS rn
FROM dbo.Table1
) AS f[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-03-01 : 11:49:31

you can use this also instead of CASE WHEN


UPDATE  f
SET     f.CustomerNo = f.CustomerNo  + COALESCE(NULLIF('_DUP' + CAST(rn AS VARCHAR(12)),'_DUP1'),'')
FROM    (
                SELECT  CustomerNo,
                        ROW_NUMBER() OVER (PARTITION BY CustomerNo ORDER BY CustomerNo) AS rn
                FROM    dbo.Table1        
        ) AS f

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

syed498
Starting Member

5 Posts

Posted - 2014-03-01 : 23:43:09

Guys..thank you for responding to my query.

But I think I did not mention in my first mail properly that, I have to check for the Customerno in Table1 and then udpate Table2 accordingly with the next _DUPXX(xx should be next number if that _DUPxx is already present in Table1)

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-03-02 : 10:04:41

Sorry thats not clear. So does that mean table1 already has customerNo values in _DUPxx format?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

syed498
Starting Member

5 Posts

Posted - 2014-03-02 : 21:33:42

Yes.. Let me once again explain my query.

I have one main table called Table1. This table has distinct customerno field and few other fields.

I have one more table Table2 which also has Customerno and few other fields.

Table2 has customerno which are repeating.

Table1:
1. CustomerNo field is not of fixed lenght(eg.,I have ABC_001,HIJKLM_001,ABC_001_DUP2,ABC_002)

Table2:
1. In this table the CustomerNo field is also not of fixed lenght.

Eg of Table1:

Customerno
ABC_001
ABC_001_DUP2
HIJKLM_001
ABC_002

Eg of Table2
ABC_001
ABC_002
ABC_001
ABC_003

Now I need a query which should check if Table2 values are present in Table1 and if it finds same entry in Table1 then it shoud add _DUPxx(xx should be the next number, so in our example ABC_001 has ABC_001_DUP2 in table1 and our table2 has two times again so our updated table2 result should look like this

My Result should look like this below.
ABC_001_DUP2
ABC_001_DUP3
ABC_002
ABC_003

syed498
Starting Member

5 Posts

Posted - 2014-03-02 : 21:35:14

Correction to my latest reply

My Result should look like this below.
ABC_001_DUP3
ABC_001_DUP4
ABC_002
ABC_003

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources