UPDATE t1
SET t1.products = REPLACE(t1.products,t2.products,'')
FROM table1 t1
INNER JOIN table2 t2 ON t1.customers = t2.customers
This will remove those products in table1 but will let double commas (when removed from the middle of the string), initial commas (where removing from the beginning of the string and final commas (when removing from the end of the string) so you'll need to run the following update to sort out this situation:UPDATE table1
SET products = CASE
WHEN LEFT(products,1)=',' THEN RIGHT(products,LEN(products)-1)
WHEN RIGHT(products,1)=',' THEN LEFT(products,LEN(products)-1)
ELSE REPLACE(products,',,',',')
END
UPDATE table1
SET products = CASE
WHEN LEFT(products,2)=', ' THEN RIGHT(products,LEN(products)-2)
WHEN RIGHT(products,2)=' ,' THEN LEFT(products,LEN(products)-2)
ELSE REPLACE(products,', ,',',')
END
NOTE: Do not run this in your Production environment before testing it in a Development environment.
UPDATE dbo.livetable
SET product = REPLACE(t1.product,t2.product,'')
FROM dbo.livetable as t1
INNER JOIN #update t2 ON t1.customers = t2.customers AND t1.location = t2.location AND t1.programId = t2.programid