CODEWITHSUNDEEP
sqlsql-serveriterationcommon-table-expressionrow-number
coding1223322
coding1223322

Reputation: 473

How to iterate through each row in table and update a column?

I want to update any duplicate string against a client and make it unique by appending a number at the end of it. Following is just a illustration of something that I have.

|Id|ClientId|String|
|1 |123     |Apple |
|2 |123     |Apple |
|3 |123     |Orange|
|4 |222     |Grapes|
|5 |222     |Grapes|

Due to the nature of data, I had to use the Row_Number() (which I am new to it) to achieve the following result. My question is how can I iterate through the following table and append a number against each client that has a duplicate string so that they are all unique.

|Id|ClientId|String|RowId|
|1 |123     |Apple |1    |
|2 |123     |Apple |2    |
|3 |123     |Orange|3    |
|4 |222     |Grapes|1    |
|5 |222     |Grapes|2    |

What I want the end result to look like

|Id|ClientId|String   |RowId|
|1 |123     |Apple 1  |1    |
|2 |123     |Apple 2  |2    |
|3 |123     |Orange   |3    |
|4 |222     |Grapes 1 |1    |
|5 |222     |Grapes 2 |2    |

If it helps following is my sql:

patternsWithNumbers AS
(
    Select row_number() over (partition by ClientId + String order by Id asc) AS RowId,
            id,
            t1.*
    from tableName t
    inner join table 2 t2 on t2.ClientId = t1.ClientId and t2.String = t1.String
)

Upvotes: 2

Views: 7130

Answers (3)

iltelko
iltelko

Reputation: 57

I would slightly modify Gordon's answer, so that first only product names are changed:

UPDATE t
SET
string = string + ' ' + CAST(t2.item_id AS VARCHAR(255))
FROM
(SELECT t.id,
ROW_NUMBER() OVER (PARTITION BY clientid, string ORDER BY id) AS item_id,
COUNT(*) OVER (PARTITION BY clientid, string) AS item_count
FROM t)
t2 
INNER JOIN t
ON t.id = t2.id
WHERE t2.item_count > 1

Then it is easy to just to add item number for the client (of course in real life you possibly have also order_id or something like that, which is needed to included in both of these queries?):

UPDATE t
SET
RowId = row_id
FROM
(SELECT t.id,
ROW_NUMBER() OVER (PARTITION BY clientid ORDER BY id) AS row_id
FROM t)
t2 
INNER JOIN t
ON t.id = t2.id

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269843

The following query calculates rowid:

  select t.*,
         (string +
          (case when count(*) over (partition by clientid, string) > 1
                then cast(row_number() over (partition by clientid, string order by id) as varchar(255))
                else 0
           end)
         ) as new_string,
         row_number() over (partition by clientid, string order by id) as rowid
  from t

Use an updatable CTE to change the table:

with toupdate as (
      select t.*,
             row_number() over (partition by clientid, string order by id) as new_rowid,
             count(*) over (partition by clientid, string) as cnt
      from t
     )
update toupdate
    set rowid = new_rowid,
        string = string + cast(new_rowid as varchar(255))
    where cnt > 1;

Upvotes: 0

DhruvJoshi
DhruvJoshi

Reputation: 17126

You can use your query in a CTE like below:

; with cte as 
( select id,clientid,string,
row_number() over(partition by clientid, string order by id asc) r
from tbl)


select 
    c1.id,
    c1.Clientid, 
    CONCAT(c1.String, case when c2.id is not null then cast(c1.r as varchar(100)) else null end),
    c1.r
from cte c1 
left join cte c2
on c2.r>1
and c1.string=c2.string 
and c1.clientid=c2.clientid
order by id asc

see working demo

Upvotes: 1

Related Questions