I am trying to change value of Consecutive AA to first AA as AA1 and second one as AA2, if only one AA then it should be as AA1, its possible to change all AA to AA1 and then search for 2nd consecutive AA1 to change it AA2 example

enter image description here

  • 2
    "I want" isn't a description of a specific issue or question you encountered while you made an attempt, with reasonable effort, to solve a problem. So edit the question and show what you have tried and explain exactly where you have what issue with it.
    – sticky bit
    Feb 9 '21 at 22:06
  • I tried a lot in my own and stuck in a similar condition, I can't show you my exact approach but I can explain that I am stuck in similar situation. Feb 10 '21 at 2:47

Assuming the [Name] column is unique, and that's the field you want to sort on (since a table is unsorted by default) this query using the ROW_NUMBER() window function should do the UPDATE you're looking for:

        Dept + CAST(ROW_NUMBER() OVER (PARTITION BY Dept ORDER BY [Name]) AS VARCHAR(10)) AS Dept, -- Generates a unique ID for each row within a Dept ordered by the [Name]
        COUNT([Name]) OVER (PARTITION BY Dept) AS DeptCount -- Counts how many dupe Dept there are for each type of Dept (used to filter out non-dupes in the UPDATE statement below)
    FROM Table1

SET T.Dept = N.Dept
FROM Table1 AS T
    ON T.[Name] = N.[Name]
WHERE N.DeptCount > 1 -- Only update Dept that are dupes
  • Its not like that, we can't sort or order by any other column Feb 10 '21 at 3:02
  • @AvinashKumar If you can't sort on any column then how do you define what row is first? (A table has no order to it without using an ORDER BY clause.)
    – J.D.
    Feb 10 '21 at 3:07
  • that is what I need to figure it out to find which one is first, somewhere it suggested to use cursor but I am unable to find it. If I use order by then third consecutive will get affected, where only 2nd consecutive need to change. that is my point Feb 10 '21 at 3:21
  • 2
    @AvinashKumar Sorry your example is not clear enough, if you could update your post to clarify why your outputted results go "AA1", "AA1", and then "AA2" instead of "AA1", "AA2", "AA3" (for rows A, D, E) that would be helpful. But regardless, if you're not allowed to order by any of the columns, then it's impossible to know which one is first (unless you were already using one of SQL Server's Auditing or Logging features, but you would've had to explicitly enable them). A cursor won't help you figure out which one is first.
    – J.D.
    Feb 10 '21 at 3:36

Not the answer you're looking for? Browse other questions tagged or ask your own question.