i'm trying to write a MySQL query for update a table, but i obtain an error. This is my query:
UPDATE mytable
SET mytable.email = (
select ps_customer.id_customer
from ps_customer
where ps_customer.email = mytable.email)
where (ps_customer.email = mytable.email)
Mysql says: #1054 - Unknown column 'ps_customer.email' in 'where clause'
I can't understand where error is. Can you help me, please?
Best Regards, Simone
Your second WHERE cannot see inside the subquery. How you are approaching this is a bit odd; if you use an UPDATE with an INNER JOIN, you can use your WHERE condition as the JOIN criteria and just SET the field in one JOINed table with a value from the other. Like so....
UPDATE mytable INNER JOIN ps_customer
ON mytable.email = ps_customer.email
SET mytable.email = ps_customer.id_customer
;
You don't need the second WHERE clause. The WHERE clause in the subquery matches the rows between the two tables (this is called a correlated subquery). But a better way to write it is as a join:
UPDATE mytable AS m
JOIN ps_customer AS c ON m.email = c.email
SET m.email = c.id_customer
