?
Solved

sql query

Posted on 2011-10-06
9
Medium Priority
?
243 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:samprg
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 28

Expert Comment

by:strickdd
ID: 36925664
0
 
LVL 5

Accepted Solution

by:
almander earned 668 total points
ID: 36925667
SELECT Row_Number() OVER(PARTITION BY F1 ORDER BY F1),
  F1,
  F2
FROM YOUR_TABLE_NAME
0
 

Author Comment

by:samprg
ID: 36925709
Nice, but I need to update F1 with number
Thanks

Result:
f1         f2
coca   coca1
coca   coca2
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 28

Assisted Solution

by:strickdd
strickdd earned 668 total points
ID: 36925834
SELECT F1,
  F2 + Row_Number() OVER(PARTITION BY F1 ORDER BY F1)
FROM YOUR_TABLE_NAME
0
 

Author Comment

by:samprg
ID: 36925919
Good, I wanna
1-Update statemnet
2- Just for duplicate
0
 
LVL 28

Expert Comment

by:strickdd
ID: 36925996
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
 
LVL 32

Assisted Solution

by:Brendt Hess
Brendt Hess earned 664 total points
ID: 36926297
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
 
LVL 32

Expert Comment

by:Brendt Hess
ID: 36926306
Ah, for the ability to edit.  You don't actually need to return ItemName from the Filt query...
0
 

Author Closing Comment

by:samprg
ID: 36931596
Thank you
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question