CODEWITHSUNDEEP
mysqlsql-updatesubquery
Nolan.K
Nolan.K

Reputation: 305

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

Upvotes: 0

Views: 4069

Answers (2)

Thomas G
Thomas G

Reputation: 10216

You have not provided enough information to get a clear answer.

If you don't set a WHERE clause in your UPDATE statement, you will update all records with the same value, which is probably not what you want

If you want to update the users table with some info on matching records in the client table, then you need to join both tables on your matching field. Something like this :

UPDATE users U, clients C
SET u.username = c.username
WHERE U.email=C.email
AND C.email IN (...)

In this example I assumed that the email address was the matching field between the 2 tables (you should adapt this), and that you provide a list of target email addresses (you can remove this)

Upvotes: 0

scx
scx

Reputation: 2789

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

Upvotes: 3

Related Questions