sql query

Hi,

I have a duplicate in item's name, so I want to add number 1,2,3,.. for any duplicate
on new field
Result:,Thanks
f1         f2
coca   coca1
coca   coca2
samprgAsked:
Who is Participating?
 
almanderCommented:
SELECT Row_Number() OVER(PARTITION BY F1 ORDER BY F1),
  F1,
  F2
FROM YOUR_TABLE_NAME
0
 
samprgAuthor Commented:
Nice, but I need to update F1 with number
Thanks

Result:
f1         f2
coca   coca1
coca   coca2
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
strickddCommented:
SELECT F1,
  F2 + Row_Number() OVER(PARTITION BY F1 ORDER BY F1)
FROM YOUR_TABLE_NAME
0
 
samprgAuthor Commented:
Good, I wanna
1-Update statemnet
2- Just for duplicate
0
 
strickddCommented:
UPDATE myTable
Set
   Column1 = Value1
WHERE
    F1 IN (SELECT F1,
  F2 + Row_Number() OVER(PARTITION BY F1 ORDER BY F1)
FROM YOUR_TABLE_NAME)
0
 
Brendt HessSenior DBACommented:
Question:  Do you only want to update the rows that have duplicates?  If so, the query is more complex.  Also, you will need to include a unique identifier to specifically target the rows needed.


UPDATE MyItems
SET ItemName = mi.ItemName + CAST(rnum as varchar(5))
FROM  MyItems mi
INNER JOIN (
    SELECT ItemName,
        ItemID,
        Row_Number() OVER(PARTITION BY ItemName) rnum
    FROM MyItems mif
    INNER JOIN (
        SELECT ItemName, Count(*) ct
        FROM MyItems mi2
        GROUP BY ItemName
        HAVING COUNT(*) > 1
        ) multi
        ON multi.itemName = mif.ItemName
    ) Filt
    ON mi.ItemID = Filt.ItemID
0
 
Brendt HessSenior DBACommented:
Ah, for the ability to edit.  You don't actually need to return ItemName from the Filt query...
0
 
samprgAuthor Commented:
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.