How to do a MySQL table UPDATE from a SELECT WHERE query.

A handy command which uses JOIN inside an UPDATE.

This command will update the status table and set level = 1 where uid has between 1 and 9 posts in the users table.

UPDATE `status` t1
    JOIN
    (
        SELECT `uid`
        FROM `users`
        WHERE `posts` >= 1
          AND `posts` < 10
    ) t2
    ON t1.uid = t2.uid
SET t1.level = 1;

This type of MySQL query is greater when you need to do a mass update but don’t want to use a backend language like PHP to combine a select loop with an update query.