manavym infotech manavym infotech - 3 months ago 8
MySQL Question

I have few same Product SKU ID in my mysql table and i want to add "-1","-2" in last of SKU ID to differentiate

I have few same Product SKU ID in my table and i want to add "-1","-2" in last of that SKU ID to differentiate. There are 16000 products in the table.

item_sku| product_name
------ | ------
L-51540 | Product 1
L-41141 | Product 2
L-55142 | Product 3
L-51143 | Product 4
L-51143 | Product 5

Answer

So it sounds like you have a column named sku with values like below and you are trying to get the newsku column?

 sku    newsku
 -------------
 100    100
 100    100-1
 200    200
 200    200-1
 200    200-2
 300    300

Then one option is to use user-defined variables:

select concat(sku, if(rn=0,"",concat("_",rn))) combinedsku
from (
  select sku,
    @rn:=if(@prevsku=sku,@rn+1,
            if(@prevsku:=sku,0,0)) rn
  from yourtable, (select @rn:=0, @prevsku:=null) t
  order by sku
  ) t
order by 1