SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Duplicates
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

syed498
Starting Member

5 Posts

Posted - 02/28/2014 :  10:09:59  Show Profile  Reply with Quote
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

Sweden
30113 Posts

Posted - 02/28/2014 :  11:23:14  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/01/2014 :  11:49:31  Show Profile  Reply with Quote
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
Go to Top of Page

syed498
Starting Member

5 Posts

Posted - 03/01/2014 :  23:43:09  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/02/2014 :  10:04:41  Show Profile  Reply with Quote
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
Go to Top of Page

syed498
Starting Member

5 Posts

Posted - 03/02/2014 :  21:33:42  Show Profile  Reply with Quote
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
Go to Top of Page

syed498
Starting Member

5 Posts

Posted - 03/02/2014 :  21:35:14  Show Profile  Reply with Quote
Correction to my latest reply

My Result should look like this below.
ABC_001_DUP3
ABC_001_DUP4
ABC_002
ABC_003
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000