0

enter image description here

enter image description here

I have this two Data Extensions and the ProductCode is the same as Product1, Product2, Product3, Product4...Product10

The problem is that there is some records of products in Product1, Product2...Product10 Data Extension that don't exist in ProductCode Data extension and I want to select only the records of products from Product1, Product2...Product10 that match the ProductCode.

I can select the records that match only for one productid but I can't get my head around how to delete it for all productid's.

SELECT
customerid
,productId1
,productId2
,productId3
,productId4
,productId5
,productId6
,productId7
,productId8
,productId9
,productId10

FROM [Data extension 1]

 LEFT JOIN [Products] b ON a.Productid1 = b.ProductCode
 WHERE b.ProductCode IS not NULL
 AND a.Productid1 is NULL
  • To start with, I the modelling of the data looks really weird. What is it that you want to achieve? And second, I don't understand what you want to do. Do you want to blank out the values of productIdX in [Data extension 1] that doesn't exists in Product? – user3274635 Oct 23 '19 at 9:11
  • I'm using this DE for a Product Recommendation Email, First DE has the product list and the second one the product details. In the product list, the Productid1,2,3..10 is the same as ProductCode from the Product details. When I'm displaying the ampscript code showing the productid1, 2,3...based on ProductCode, because some values from Productid1, 2, 3 don't exist in ProductCode I get an error so I want to delete all the values from Productid1, 2, 3..10 that don't match the productCode so if productid1 832732 for Customerid 5031 doesn;t match productcode, delete it – Duicug Oct 23 '19 at 9:21
  • With the modelling you have now. You would have to compare each productId with ProductCode in ProductDE. Which means you have to join [Data extension 1] with Products 10 times. – user3274635 Oct 23 '19 at 10:10
  • There is no "Delete from" function in SFMC's SQL. You will need to replace the current data using an Overwrite function, or by exporting/importing new data. Refer to this answer: salesforce.stackexchange.com/questions/281256/… – Cameron Robert Oct 23 '19 at 11:55
  • Hi, Cameron. I have updated my question. I meant to say SELECT and not delete – Duicug Oct 28 '19 at 10:36
2

This is far from best practice and could 'choke' if you have a high volume in your DEs. BUT the good thing is you can split it out into multiple queries fairly easily by only doing a couple columns at a time instead of all 10.

So basically what I did is create 10 case statements that look for the productId inside of the Products DE. If its found, then it displays the productId, but if not then it fills it in as an empty string.

SELECT
 de.customerid
,CASE
    WHEN productId1 IN (SELECT TOP 1 b.ProductCode FROM Products b WHERE b.ProductCode = de.productId1) THEN
    productId1
    ELSE
    ''
END as productId1
,CASE
    WHEN productId2 IN (SELECT TOP 1 b.ProductCode FROM Products b WHERE b.ProductCode = de.productId2) THEN
    productId2
    ELSE
    ''
END as productId2
,CASE
    WHEN productId3 IN (SELECT TOP 1 b.ProductCode FROM Products b WHERE b.ProductCode = de.productId3) THEN
    productId3
    ELSE
    ''
END as productId3
,CASE
    WHEN productId4 IN (SELECT TOP 1 b.ProductCode FROM Products b WHERE b.ProductCode = de.productId4) THEN
    productId4
    ELSE
    ''
END as productId4
,CASE
    WHEN productId5 IN (SELECT TOP 1 b.ProductCode FROM Products b WHERE b.ProductCode = de.productId5) THEN
    productId5
    ELSE
    ''
END as productId5
,CASE
    WHEN productId6 IN (SELECT TOP 1 b.ProductCode FROM Products b WHERE b.ProductCode = de.productId6) THEN
    productId6
    ELSE
    ''
END as productId6
,CASE
    WHEN productId7 IN (SELECT TOP 1 b.ProductCode FROM Products b WHERE b.ProductCode = de.productId7) THEN
    productId7
    ELSE
    ''
END as productId7
,CASE
    WHEN productId8 IN (SELECT TOP 1 b.ProductCode FROM Products b WHERE b.ProductCode = de.productId8) THEN
    productId8
    ELSE
    ''
END as productId8
,CASE
    WHEN productId9 IN (SELECT TOP 1 b.ProductCode FROM Products b WHERE b.ProductCode = de.productId9) THEN
    productId9
    ELSE
    ''
END as productId9
,CASE
    WHEN productId10 IN (SELECT TOP 1 b.ProductCode FROM Products b WHERE b.ProductCode = de.productId10) THEN
    productId10
    ELSE
    ''
END as productId10
FROM [Data extension 1] de

To help speed things up as this is essentially now doing 10 subqueries, I used this SELECT TOP 1 b.ProductCode FROM Products b WHERE b.ProductCode = de.productId10 where I only select a single record and assign a where clause to match ProductCode to the productId so it should only return a record if its a match. This will greatly reduce the processing required and hopefully keep it all in a single query for you.

If not, you can do something like the following:

SELECT
 de.customerid
,CASE
    WHEN productId1 IN (SELECT TOP 1 b.ProductCode FROM Products b WHERE b.ProductCode = de.productId1) THEN
    productId1
    ELSE
    ''
END as productId1
,CASE
    WHEN productId2 IN (SELECT TOP 1 b.ProductCode FROM Products b WHERE b.ProductCode = de.productId2) THEN
    productId2
    ELSE
    ''
END as productId2
,CASE
    WHEN productId3 IN (SELECT TOP 1 b.ProductCode FROM Products b WHERE b.ProductCode = de.productId3) THEN
    productId3
    ELSE
    ''
END as productId3
,CASE
    WHEN productId4 IN (SELECT TOP 1 b.ProductCode FROM Products b WHERE b.ProductCode = de.productId4) THEN
    productId4
    ELSE
    ''
END as productId4
,CASE
    WHEN productId5 IN (SELECT TOP 1 b.ProductCode FROM Products b WHERE b.ProductCode = de.productId5) THEN
    productId5
    ELSE
    ''
END as productId5
FROM [Data extension 1] de

as query 1 and then:

SELECT
 de.customerid
,CASE
    WHEN productId6 IN (SELECT TOP 1 b.ProductCode FROM Products b WHERE b.ProductCode = de.productId6) THEN
    productId6
    ELSE
    ''
END as productId6
,CASE
    WHEN productId7 IN (SELECT TOP 1 b.ProductCode FROM Products b WHERE b.ProductCode = de.productId7) THEN
    productId7
    ELSE
    ''
END as productId7
,CASE
    WHEN productId8 IN (SELECT TOP 1 b.ProductCode FROM Products b WHERE b.ProductCode = de.productId8) THEN
    productId8
    ELSE
    ''
END as productId8
,CASE
    WHEN productId9 IN (SELECT TOP 1 b.ProductCode FROM Products b WHERE b.ProductCode = de.productId9) THEN
    productId9
    ELSE
    ''
END as productId9
,CASE
    WHEN productId10 IN (SELECT TOP 1 b.ProductCode FROM Products b WHERE b.ProductCode = de.productId10) THEN
    productId10
    ELSE
    ''
END as productId10
FROM [Data extension 1] de

for the second one. As this is set to be 'Update' the blank columns (empty strings) will overwrite whatever value was previously in there, essentially deleting them.

If necessary you can further break them up, but you will need to include customerid in each to make sure to correctly define which record is being updated.

Below are my results

Original:
enter image description here

Products DE:
enter image description here

Results:
enter image description here

| improve this answer | |
  • That's perfect. Thanks very much! – Duicug Oct 28 '19 at 13:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.