I used to know how to do this but a lack of practice made me lose it.
I am trying to update usernames from a table by comparing matching email in another.
basically the first table has username empty, while the other has username and emails filled.
here is my wrong query :
UPDATE users SET username = (SELECT Username FROM clients WHERE email in mail)
I would suggest update with using JOIN with UPDATE, something like this should work
UPDATE users INNER JOIN #your relationship / for example clients ON (users.id = clients.user_id) SET users.username = clients.email WHERE users.username IS NULL
Just make sure ON clause is correct relation that you have between users and clients and it should update all records in users username column with email from clients