How to assign guest orders to registered customers by email via SQL?

Question

I'm using this SQL below to manually assign guest orders to registered users.

UPDATE sales_order SET customer_id = {YOUR CUSTOMER ID}, customer_is_guest = 0 where entity_id = {YOUR ORDER ID};
UPDATE sales_order_grid SET customer_id = {YOUR CUSTOMER ID} where entity_id = {YOUR ORDER ID};
UPDATE downloadable_link_purchased SET customer_id = {YOUR CUSTOMER ID} WHERE order_id = {YOUR ORDER ID};

How could I automatically assign every order made by guest users assigned to registered clients by email?

Answer 1

You can also do it programmatically. But if you need solution for SQL then you can use commands like these:

UPDATE sales_order o
INNER JOIN sales_order_address oa ON o.entity_id=oa.parent_id
INNER JOIN customer_entity c ON oa.email=c.email
SET o.customer_id = c.email, o.customer_is_guest = 0 
WHERE oa.address_type='shipping'
AND c.email = {YOUR CUSTOMER EMAIL};

UPDATE sales_order_grid og
INNER JOIN sales_order_address oa ON og.entity_id=oa.parent_id
INNER JOIN customer_entity c ON oa.email=c.email
SET og.customer_id = c.email
WHERE oa.address_type='shipping'
AND c.email = {YOUR CUSTOMER EMAIL};


UPDATE downloadable_link_purchased lp
INNER JOIN sales_order_address oa ON lp.order_id=oa.parent_id
INNER JOIN customer_entity c ON oa.email=c.email
SET lp.customer_id = c.email
WHERE oa.address_type='shipping'
AND c.email = {YOUR CUSTOMER EMAIL};

Note: It's just my idea, I haven't tested these commands by myself. Please make a backup of your database before applying these :)