CODEWITHSUNDEEP
sqlsql-server-2008
user710502
user710502

Reputation: 11469

Updating duplicate column with value

I have a table with CUSTOMERNAME and REDIRECTNAME columns in my REDIRECTS table as follow

ID    NAME        REDIRECTLINK

1    Gregory     XYUS_555
2    Sam         VYU_787
3    Smith       XYUS_555
4    John        PPIU_987

So basically I want to update the duplicate and append a number to it like for Smith it should be

Smith     XYUS_555_01

Which will take care of the duplicate. I am just not sure how to go about updating only the duplicate. I have the following to find the duplicates:

SELECT
    REDIRECTLINK, COUNT(*) dupcount
FROM
    REDIRECTS
WHERE 
    REDIRECTLINK IS NOT NULL
GROUP BY
    REDIRECTLINK
HAVING 
    COUNT(*) > 1

This tells me how many dups per redirect link, but how can I go about updating the the dups?

UPDATE REDIRECTS
SET REDIRECTLINK = REDIRECTLINK + '01" //NOT SURE HOW TO APPROACH THIS
WHERE REDIRECTLINK IN (
  SELECT REDIRECTLINK
  FROM REDIRECTS
  GROUP BY REDIRECTLINK
  HAVING ( COUNT(REDIRECTLINK) > 1 )
)

I am having issues on the code above because i am not sure how link it to the duplicate found and not both.

Upvotes: 2

Views: 2631

Answers (3)

user2970362
user2970362

Reputation: 384

First determine the subnumber of the RedirectLink by using ROW_NUMBER() (and subtract 1 to make it start at zero). Then use it in an update to update all records that have a subnumber above zero.

with NT as  (
select 
  ID,
  Row_Number() over (PARTITION BY RedirectLink ORDER BY ID)-1 as Nr
from Table1
)
update T  
  set T.RedirectLink = T.RedirectLink + '_' + cast(NT.Nr as varchar)
FROM Table1 T
JOIN NT ON (NT.ID = T.ID)
where Nr>0

Upvotes: 1

Yosi Dahari
Yosi Dahari

Reputation: 7009

You can UPDATE using JOIN:

;WITH cte AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY Id DESC) AS rn
   FROM REDIRECTS 
)

UPDATE REDIRECTS 
SET REDIRECTLINK = REDIRECTLINK + '01' 
FROM cte
INNER JOIN REDIRECTS
    ON REDIRECTS.Id = cte.Id
WHERE cte.rn = 1;

You can see this post for more details

Upvotes: 1

Andrew
Andrew

Reputation: 8758

If I understand correctly, you can use row_number() to identify your duplicates, and then update the ones where row_number is > 1.

select
id,
redirectlink,

row_number() over (partition by redirectlink order by id)
from
table1

SQL Fiddle

Upvotes: 0

Related Questions