Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 324
  • Last Modified:

Updating fields in one table via querying another table

hi guys i am trying to populate how many products are available within a certain category in Mysql database but i am unsure of the sql query to run. the 2 table examples are below.

Table 1 with fields id/category/product. field "id" is an auto increment field and can be ignored.
the layout is referenced below
1/1/1
2/1/2
3/1/3
4/1/4
5/4/5

i need to somehow get a total amount of products that are within each category i.e in the example above there would be a total products of 4 and they are within category 1 and 1 item within category 4


Table 2 has fields named id/category/product. the category names only appear once so go from 1 to 20 for example but i must update the field within this table named "product" and put the amount of products found within table 1 into each category
1/pens/0
2/rulers/0
3/mats/0
4/inks/0

So if the query ran i would expect an update of field "product" in table 2 to then change to the below
1/pens/4
2/rules/0
3/mats/0
4/inks/1

hope this is easy enough to follow and thank you in advance for your help
0
TG-Steve
Asked:
TG-Steve
  • 2
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
TG-Steve,

Yes, you can use an update with a join like so in MySQL:

UPDATE Table2 t2, (SELECT category, COUNT(product) `cnt` FROM Table1) t1
SET t2.product = t1.cnt
WHERE t2.category = t1.category
;

Open in new window


Regards,
Kevin
0
 
Kevin CrossChief Technology OfficerCommented:
Sorry, forgot the GROUP BY:
UPDATE Table2 t2, (SELECT category, COUNT(product) `cnt` FROM Table1 GROUP BY category) t1
SET t2.product = t1.cnt
WHERE t2.category = t1.category
;

Open in new window

0
 
TG-SteveAuthor Commented:
Tested today and works perfect thankyou
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now