magento newsletter_subscriber query

Question

I'm trying to get update customers to subscribers in my custom table:

UPDATE `my_contacts_and_subscribers` AS `dc` LEFT JOIN `newsletter_subscriber` AS `ns` ON dc.customer_id = ns.customer_id SET `dc`.`is_subscriber` = 1, `dc`.`subscriber_status` = 1 WHERE (ns.subscriber_status =1)

However this query is hanging, not finishing. Is there anything wrong with it?

Any help appreciated!

Answer 1

In general, you should always paste more information about your project in order to better understand it. Here schema structure is missing for my_contacts_and_subscribers table.

Try with following query:

UPDATE my_contacts_and_subscribers
SET is_subscriber = 1, subscriber_status = 1
WHERE EXISTS (
    SELECT 1 FROM customer_entity 
    WHERE newsletter_subscriber.customer_id = my_contacts_and_subscribers.customer_id 
    AND newsletter_subscriber. subscriber_status = 1
);

Also, you've mentioned that it takes too long. How big are your tables my_contacts_and_subscribers and newsletter_subscriber? Paste results of:

SELECT count(*) FROM my_contacts_and_subscribers;
SELECT count(*) FROM newsletter_subscriber;

And, at the end, you're maybe missing indexes, that's why join goes to slow. You can check your execution plan by putting word EXPLAIN in front of your query.

Check do you have index in my_contacts_and_subscribers on customer_id field. You can add it by executing:

ALTER TABLE `my_contacts_and_subscribers` ADD INDEX `idx_my_contacts_customer_id` (`customer_id`);

It can speed up this update query a lot.

Answer 2

I checked your SQL in a syntax checker and looks good. Try doing this using a SELECT statement first before running the UPDATE and strip it down to bare bones. Sorry I can't help without more info.

https://www.piliapp.com/mysql-syntax-check/