How to count number of retweets and update database accordingly?
===

Consider the following tables:

    tweets                                retweets
    ----------------------------------    --------------------
    id  id_str             retweet_cnt    id id_str
    ----------------------------------    --------------------
    1   46604457684901888                 1  46604457684901888
    2   46556513103388672                 2  46604457684901888
    3   46294368197427200                 3  46604457684901888
                                          4  46556513103388672
                                          5  46556513103388672

I would like to `UPDATE` `tweets.retweet_cnt` by counting for each (varchar) `id_str` how many times it occurs in `retweets`. Can anybody help me with the query for this? Important: it is possible for in `id_str` **not** to appear in `retweets` (as as the case with `46294368197427200` in the example). In that case, zero (`0`) should be returned.

Any help is appreciated.