Mysql PHP query updation

I have a two tables cstomers and orders table. These tables contain 20,000 records

What i need is i want to update the orders table with the customer_id based on the email. Can i use subquery and php OR it can be accomplished using a single query and efficient method?

customers table

id  email     name
--------------
1 | email1 | name1
2 | email2 | name3
3 | email3 | name3

Orders table

order_id  customer_id   email    product name  group_id
-------------- -----------------------------------------
1         1             email1    prod1          0
2         (NULL)        email1    prod1          1
3         1             email1    prod1     开发者_如何学运维     0
4         (NULL)        email2    prod1          1
5         2             email2    prod1          0
6         2             email2    prod1          1
7         (NULL)        email2    prod1          1
2         (NULL)        email1    prod1          1

• group_id =0 means that the customer has account
• group_id =1 means that the customer does not have account

---

UPDATE customers c INNER JOIN orders o ON o.customer_id = c.id
SET o.customer_id = c.id
WHERE o.email = c.email

That should do it. Because this is an INNER JOIN, it just won't update orders for user accounts that don't exist in the customers table.

---

Maybe this (untested)

UPDATE orders o SET o.customer_id = (SELECT c.customer_id FROM customers c WHERE c.email = o.email)