MySQL sub query query select query inside update query

Question

I have 2 tables: tbl_taxclasses, tbl_taxclasses_regions

This one-to-many relationship is where the primary identifier for the entry is classid . I have a column inside the first regionscount table

So, I create a tax class in table 1. Then I add the regions / states in table 2, assigning a classid for each area.

I execute a SELECT statement to count areas with the same class, and then I execute an UPDATE statement on tbl_taxclasses with that number. I am updating the regionscount column.

This means that I am writing 2 queries. This is fine, but I was wondering if there is a way to make a SELECT statement inside an UPDATE statement, for example:

 UPDATE `tbl_taxclasses` SET `regionscount` = [SELECT COUNT(regionsid) FROM `tbl_taxclasses_regions` WHERE classid = 1] WHERE classid = 1 

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

Answer 1

You can use the uncorrelated subquery to do the job 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 = rn WHERE c.classid = 1 

Answer 2

Turns out I really knew.

It 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 the brackets [] with the bracket ().