Mysql – updating values in one table if depending table row is affected

MySQL

there are two tables

structure is like:

product_table

item_id product_id count_subscriber
1          2           4
2          3           5

subscriber_table

item_id subscriber_id product_id
1         115           2
2         145           2
3         84            3

what i want to achieve is if i add/delete one item_id from subcriber_table this should update the value in count_subscriber (increment/decrement) of product_table where product_id matches.

is this possible to do this in one query? how should i achieve this in one query? right now what i am doing is first deleting the item_id in subscriber_table and than updating the count_subscriber in product_table and same for addition.

please advise.
thank you for your time.

Best Answer

Not sure if this will fit in your situation, but a mysql VIEW should work for you, and make things easier.

You could create a VIEW called product_table, based on a select over subscriber_table, like this:

CREATE VIEW product_table AS SELECT item_id, product_id, COUNT(*) AS
count_subscriber  FROM subscriber_table GROUP BY item_id, product_id

Using this, you will not need to update manually product_table data on every change made over subscriber_table.

Related Question