CODEWITHSUNDEEP
mysqlsql
M Shahzad Khan
M Shahzad Khan

Reputation: 935

Need mysql update select query to be optimized

I have tables subcategory and listing_category. listin_category table has different records which include subcategory and category as well. What i want is to find count for all the subcategory for specific category say category=3, in my listing_Category table and update it in subcategory table. What i am trying to do it

UPDATE `subcategories` SET total_count = ( SELECT count( * )
FROM `listings_category`
WHERE category =3 and subcategory IN (subcategories.id)
WHERE category_id =3 and id=subcategories.id

I am having problem in making array subcategories.id. Any help will be appreciated. If any one suggest more optimized query that will be great. Thanks in advance

Upvotes: 0

Views: 89

Answers (1)

M Shahzad Khan
M Shahzad Khan

Reputation: 935

I solved it myself :) I wonder why people vote negative for meaningful question. Here is the solution.

update subcategories, 
       (
         SELECT subcategory,count(subcategory) as count 
           FROM `listings_category` 
          group by subcategory
       ) sb  
  SET subcategories.total_count = sb.count 
where subcategories.id = sb.subcategory

Upvotes: 1

Related Questions