Nolan.K Nolan.K - 4 years ago 110
MySQL Question

MySQL : update with Where clause in subquery

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)


email is from my clients table, mail is from my users table

scx scx
Answer Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download