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.