0

Currently we have to run the following SQL Query to find all customers that have not been assigned access to a downloadable product.

SELECT c.entity_id as customer_id, dp.purchased_id as purchased_id
FROM magento_dev.sales_order  as so
JOIN magento_dev.sales_order_address  as soa on so.entity_id = soa.parent_id
JOIN magento_dev.customer_entity as c on c.email = soa.shipping_email
JOIN magento_dev.downloadable_link_purchased as dp on so.increment_id = dp.order_increment_id
where
so.customer_id = 12345 and
so.created_at > '2021-04-01' and
soa.shipping_email is not null and
dp.customer_id != c.entity_id
;

Which produces this:

customer_id purchased_id
99999   55555

Then with the results, we manually have to go through, and for each customer_id, and purchase_id run the following query:

UPDATE `magento_dev`.`downloadable_link_purchased` SET `customer_id` = '99999' WHERE (`purchased_id` = '55555');

So for the SQL Ninja's, any suggestions on how to make this a single query that finds the mismatched assignments and then does the update/insert at once?

Mike Miller
  • 75
  • 8
  • Are you sure `dp.customer_id != c.entity_id` is the correct condition? That doesn't find customers that haven't been assigned to a product. It joins with every product that was assigned to a different customer. – Barmar Sep 10 '21 at 20:15
  • Or put another way, it joins with every customer other than the one assigned to to the product. – Barmar Sep 10 '21 at 20:16
  • See https://stackoverflow.com/questions/21633115/return-row-only-if-value-doesnt-exist?lq=1 for how to find rows that don't have a matching row in another table. – Barmar Sep 10 '21 at 20:16
  • Yes, because this is being used for a reseller to buy a product, then assign the downloadable rights to another user. However, the Magento system messes it up, so we have to manually run this and fix it. – Mike Miller Sep 10 '21 at 20:19

2 Answers2

3

Just put all the JOINs in the UPDATE query.

UPDATE `magento_dev`.`downloadable_link_purchased` AS dp1
JOIN magento_dev.sales_order  as so
JOIN magento_dev.sales_order_address  as soa on so.entity_id = soa.parent_id
JOIN magento_dev.customer_entity as c on c.email = soa.shipping_email
JOIN magento_dev.downloadable_link_purchased as dp 
    ON so.increment_id = dp.order_increment_id 
        AND dp.customer_id != c.entity_id 
        AND dp1.purchased_id = dp.purchased_id
SET dp1.customer_id = c.entity_id
where
    so.customer_id = 12345 and
    so.created_at > '2021-04-01' and
    soa.shipping_email is not null
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Ok how would that work where the customer_id and purchase_id are variable based on the joins? I edited my question above to show what is returned from the first query. – Mike Miller Sep 10 '21 at 20:15
  • 1
    I didn't realize that the output of the first query was being used as the values to assign and match in the update. I've updated the answer. – Barmar Sep 10 '21 at 20:23
2

Can be done like sub query and join with the table which should be updated,

UPDATE 
    magento_dev.downloadable_link_purchased F
JOIN 
    (SELECT c.entity_id as customer_id, dp.purchased_id as purchased_id
        FROM magento_dev.sales_order  as so
        JOIN magento_dev.sales_order_address  as soa on so.entity_id = soa.parent_id
        JOIN magento_dev.customer_entity as c on c.email = soa.shipping_email
        JOIN magento_dev.downloadable_link_purchased as dp on so.increment_id = dp.order_increment_id
    WHERE
        so.customer_id = 12345 and
        so.created_at > '2021-04-01' and
        soa.shipping_email is not null and
        dp.customer_id != c.entity_id) S
    ON F.purchased_id = S.purchased_id
SET F.customer_id = S.customer_id;
TiginMannuel
  • 31
  • 5