Patrick Mahoney Patrick Mahoney - 2 years ago 61
SQL Question

How can I append a unique identifier onto rows with same column value

It's hard to explain my question, but here is the issue. I have a table of 350k records. The table is filled with skus and other part information. There are many rows that have the same sku. For example there are 5 rows with the sku 45666. I need to put a unique identifier on each of these. e.g 45666~1, 45666~2, etc... The solution I've come up with was using a while loop in a while loop and just doing them one at a time like this

while($i -le $array.length) {
$mfp = $array.itemnumber[$i]
while($array.itemnumber[$i] = $mfp) {
Invoke-SQLCmd -ServerInstance $Server -Database $Database -Query "update whiproducts set number = $j"


What I would do after this is concat number with the sku when I select it. The problem here is this has been running for like 30 minutes which just won't work well with the way I'm going about this project. Is there some quick and easy way to do this I'm missing!?

Thanks for any help!

Answer Source

Give this query a try (please don't run this on production data before testing or backing up):

update t
set t.sku = t.sku + '~' + cast(t.RowNumber as varchar)
from (
    select sku, row_number() over(partition by sku order by sku) as RowNumber
    from whiproducts) t
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download