4

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 = 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)

coffeemonitor
  • 12,780
  • 34
  • 99
  • 149
  • This gives you error [1093 - you can't UPDATE or DELETE using a subquery if your subquery queries the table you are deleting from](http://stackoverflow.com/questions/4484779/mysql-getting-around-error-1093) – bobobobo Aug 27 '11 at 18:51
  • Can I ask what is the Primary Key of the `taxclass` table? – ypercubeᵀᴹ Aug 27 '11 at 19:01

2 Answers2

0

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
Cᴏʀʏ
  • 105,112
  • 20
  • 162
  • 194
0

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 ().

Mohd Farid
  • 2,010
  • 1
  • 17
  • 23
coffeemonitor
  • 12,780
  • 34
  • 99
  • 149