UPDATE with a JOIN & GROUP

I want to update all user records with the number of ema开发者_Python百科ils associated to that user. So I have

| userid | name | emailcount |

and

| userid | emaildata

I am trying to make a single UPDATE query which which will fill the emailcount with the number of emails that user has.

I have tried using a single UPDATE but can't make it work; do I need to use a subquery somehow to do this?

As Elliot suggests, you can drop the column emailcount and generate the value dynamically with a query like this:

select userid, name, coalesce(ec.count, 0) as emailcount
from User u
left outer join (
    select userid, count(*) as count
    from Email
    group by userid
) ec on u.userid = ec.userid

If you want to do this action I suggest this query, but it is not tested on MySQL, I don't have the access at this moment. I hope that it is correct if not please others can correct me

UPDATE user SET emailcount = (SELECT count(*) FROM emaildata WHERE emaildata.userid  user.userid)