Welcome to Vortx Community Forum, where you can ask questions and receive answers from the staff at Vortx and other members of the community.

If you had a user account on our previous forums website, you will need to register a new account here.

Learn more about...

AspDotNetStorefront
DotFeed

SQL to update GTIN field in 9.4.0.0

Hello,

Does anybody have/know the SQL to update the ProductVariant table GTIN field based the Product table SKU field?

MS 9.4.0.0

Thanks,

Casey
asked Nov 25, 2013 in MultiStore by Vegarari (350 points)

2 Answers

0 votes

Do you mean look up the product UPC (which is what a GTIN is it would seem (http://www.gtin.info/)) by SKU?  Or are you referring to some way of structuring SKUs such that a GTIN/UPC could be generated?

answered Dec 3, 2013 by Vortx Joe (550 points)
0 votes
if you have your UPC field in the product.sku field then it would be

update productvariant
set gtin = p.sku
from productvariant as pv with(nolock)
inner join product as p with(nolock)
on pv.productid = p.productid

this would mean that if you have variants for your products (ie: more than one ProductVariant for your Product), then there will be duplicate GTIN's.

we use the ProductVariant.skusuffix for our UPC. so ours would be like this:

update productvariant
set gtin = pv.skusuffix
from productvariant as pv with(nolock)
inner join product as p with(nolock)
on pv.productid = p.productid
where len(pv.skusuffix) = 12

 

** I have to make sure that our "custom" UPC's don't get added to the GTIN...that's why I added the where clause.
answered Dec 3, 2013 by Mikecali (1,525 points)
...