Update a product

Updates an existing product identified by its Id. You can modify attributes like Title, BodyHtml, Status, and Tags.

This example also shows how to update product images and variants by providing JSON data for the Images and Variants columns.

Standard SQL query example

This is the base query accepted by the connector. To execute it in SQL Server, you have to pass it to the Data Gateway via a Linked Server. See how to accomplish this using the examples below.

UPDATE Products
SET Title='Ice Cream - Updated'
 , Status='draft' --active, archived, draft  
 , BodyHtml='<strong>Very yummy ice cream - updated!</strong>'  
--first image becomes main image if you supply multiple images 
--upload multiple images from URL (set "src")
 , Images='[	
	{"src":"https://zappysys.com/images/tech/google-analytics-logo.png"},
	{"src":"https://zappysys.com/images/tech/web-api-logo.png"}
  ]'
--OR upload multiple local image files (set "attachment")
--, Images='[	
--	 {"attachment":"<<c:\temp\icecream_1.png,FUN_FILE_BASE64ENC>>"},
--	 {"attachment":"<<c:\temp\icecream_2.png,FUN_FILE_BASE64ENC>>"}
--  ]'

 , Variants='[
	{"price":20.5, "option1":"Chocolate","option2":"Small","sku":"ICE-CHO-SML","inventory_quantity":300},
	{"price":21.5, "option1":"Vanilla","option2":"Small","sku":"ICE-VNL-MED","inventory_quantity":110}
  ]'
, PublishedScope='global' --or web
, Vendor ='IceGlobal'
, ProductType ='Cold Food'
, Tags ='["Frozen","Seasonal","Dad''s Fav"]'
--Update SEO URL
 ,UrlHandle='ice-cream-51' 
--Update SEO title / description
, SEOTitle='Yum Ice Cream SEO Title-update'  
, SEODescription='Yum Ice Cream SEO description-update'  
Where Id=7348335771748

Using OPENQUERY in SQL Server

SELECT * FROM OPENQUERY([LS_TO_SHOPIFY_IN_GATEWAY], 'UPDATE Products
SET Title=''Ice Cream - Updated''
 , Status=''draft'' --active, archived, draft  
 , BodyHtml=''<strong>Very yummy ice cream - updated!</strong>''  
--first image becomes main image if you supply multiple images 
--upload multiple images from URL (set "src")
 , Images=''[	
	{"src":"https://zappysys.com/images/tech/google-analytics-logo.png"},
	{"src":"https://zappysys.com/images/tech/web-api-logo.png"}
  ]''
--OR upload multiple local image files (set "attachment")
--, Images=''[	
--	 {"attachment":"<<c:\temp\icecream_1.png,FUN_FILE_BASE64ENC>>"},
--	 {"attachment":"<<c:\temp\icecream_2.png,FUN_FILE_BASE64ENC>>"}
--  ]''

 , Variants=''[
	{"price":20.5, "option1":"Chocolate","option2":"Small","sku":"ICE-CHO-SML","inventory_quantity":300},
	{"price":21.5, "option1":"Vanilla","option2":"Small","sku":"ICE-VNL-MED","inventory_quantity":110}
  ]''
, PublishedScope=''global'' --or web
, Vendor =''IceGlobal''
, ProductType =''Cold Food''
, Tags =''["Frozen","Seasonal","Dad''''s Fav"]''
--Update SEO URL
 ,UrlHandle=''ice-cream-51'' 
--Update SEO title / description
, SEOTitle=''Yum Ice Cream SEO Title-update''  
, SEODescription=''Yum Ice Cream SEO description-update''  
Where Id=7348335771748')

Using EXEC in SQL Server (handling larger SQL text)

The major drawback of OPENQUERY is its inability to incorporate variables within SQL statements. This often leads to the use of cumbersome dynamic SQL (with numerous ticks and escape characters).

Fortunately, starting with SQL 2005 and onwards, you can utilize the EXEC (your_sql) AT [LS_TO_SHOPIFY_IN_GATEWAY] syntax.

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE Products
SET Title=''Ice Cream - Updated''
 , Status=''draft'' --active, archived, draft  
 , BodyHtml=''<strong>Very yummy ice cream - updated!</strong>''  
--first image becomes main image if you supply multiple images 
--upload multiple images from URL (set "src")
 , Images=''[	
	{"src":"https://zappysys.com/images/tech/google-analytics-logo.png"},
	{"src":"https://zappysys.com/images/tech/web-api-logo.png"}
  ]''
--OR upload multiple local image files (set "attachment")
--, Images=''[	
--	 {"attachment":"<<c:\temp\icecream_1.png,FUN_FILE_BASE64ENC>>"},
--	 {"attachment":"<<c:\temp\icecream_2.png,FUN_FILE_BASE64ENC>>"}
--  ]''

 , Variants=''[
	{"price":20.5, "option1":"Chocolate","option2":"Small","sku":"ICE-CHO-SML","inventory_quantity":300},
	{"price":21.5, "option1":"Vanilla","option2":"Small","sku":"ICE-VNL-MED","inventory_quantity":110}
  ]''
, PublishedScope=''global'' --or web
, Vendor =''IceGlobal''
, ProductType =''Cold Food''
, Tags =''["Frozen","Seasonal","Dad''''s Fav"]''
--Update SEO URL
 ,UrlHandle=''ice-cream-51'' 
--Update SEO title / description
, SEOTitle=''Yum Ice Cream SEO Title-update''  
, SEODescription=''Yum Ice Cream SEO description-update''  
Where Id=7348335771748'
EXEC (@MyQuery) AT [LS_TO_SHOPIFY_IN_GATEWAY]