Hi RPA,
You need to make use of Split Function for spliting the values by comma into table so that you can delete productId from the SellerProduct table
For Split Function refer below article.
Refer below sample query.
SQL
DECLARE @SellerProductTable AS TABLE (Id INT IDENTITY(1,1),IdSeller INT,IdProduct VARCHAR(50))
INSERT INTO @SellerProductTable VALUES(1,'1,2,3')
INSERT INTO @SellerProductTable VALUES(2,'4,5,6')
DECLARE @CustomerProductTable AS TABLE(Id INT IDENTITY(1,1),IdSeller INT,IdProduct VARCHAR(50))
DECLARE @IdSeller INT, @IdProduct INT,@SellerIdProduct VARCHAR(50)
SET @IdSeller = 1
SET @IdProduct = 2
CREATE TABLE #Temp(Item VARCHAR(50))
INSERT INTO #Temp
SELECT Item FROM dbo.SplitString((SELECT IdProduct FROM @SellerProductTable WHERE IdSeller = @IdSeller ),',')
IF EXISTS (SELECT IdSeller FROM @CustomerProductTable WHERE IdSeller = @IdSeller)
BEGIN
UPDATE @CustomerProductTable
SET IdProduct = IdProduct + ',' + CONVERT(VARCHAR,@IdProduct)
WHERE IdSeller = @IdSeller
DELETE FROM #Temp
WHERE Item = @IdProduct
UPDATE @SellerProductTable
SET IdProduct = (SELECT STUFF((SELECT ', ' + CAST(Item AS VARCHAR(10)) [text()]
FROM #Temp
FOR XML PATH(''), TYPE)
.value('.','NVARCHAR(MAX)'),1,2,' '))
WHERE IdSeller = @IdSeller
END
ELSE
BEGIN
INSERT INTO @CustomerProductTable VALUES(@IdSeller,CONVERT(VARCHAR,@IdProduct))
DELETE FROM #Temp
WHERE Item = @IdProduct
UPDATE @SellerProductTable
SET IdProduct = (SELECT STUFF((SELECT ', ' + CAST(Item AS VARCHAR(10)) [text()]
FROM #Temp
FOR XML PATH(''), TYPE)
.value('.','NVARCHAR(MAX)'),1,2,' '))
WHERE IdSeller = @IdSeller
END
SELECT * FROM @SellerProductTable
SELECT * FROM @CustomerProductTable
DROP TABLE #Temp