MySQL sub query select statement inside Update query

I have 2 tables: tbl_taxclasses, tbl_taxclasses_regions

This is a one to many relationship, where the main record ID is classid. I have a column inside the first table called regionscount

So, I create a Tax Class, in table 1. Then I add regions/states in table 2, assigning the classid to each region.

I perform a SELECT statement to count the regions with that same classid, and then I perform an UPDATE statement on tbl_taxclasses with that number. I update the regionscount column.

This means I'm writing 2 queries. Which is fine, but I was wondering if there was a way to do a SELECT statement inside the UPDATE statement, like this:

UPDATE `tbl_taxclasses` SET `regionscount` = [SELECT COUNT(regionsid) FROM `tbl_taxclasses_regions` WHERE classid = 1] WHERE classid = 开发者_StackOverflow中文版1

I'm reaching here, since I'm not sure how robust MySQL is, but I do have the latest version, as of today. (5.5.15)

---

You could use a non-correlated subquery to do the work for you:

UPDATE 
    tbl_taxclasses c 
INNER JOIN (
    SELECT 
        COUNT(regionsid) AS n 
     FROM 
        tbl_taxclasses_regions 
     GROUP BY 
        classid
) r USING(classid) 
SET 
    c.regionscount = r.n
WHERE
    c.classid = 1

---

Turns out I was actually guessing right.

This works:

 UPDATE `tbl_taxclasses` 
 SET `regionscount` = (
      SELECT COUNT(regionsid) AS `num` 
      FROM `tbl_taxclasses_regions` 
      WHERE classid = 1) 
 WHERE classid = 1 LIMIT 1

I just needed to replace my brackets [] with parenthesis ().