updating values in one table if depending table row is affected

Question

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.

Answer 1

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.

Answer 2

You could use a trigger :

CREATE TRIGGER after_insert_subscriber AFTER INSERT ON subscriber_table
FOR EACH ROW
BEGIN
UPDATE product_table SET count_subscriber = count_subscriber + 1 WHERE item_id = NEW.item_id AND product_id = NEW.product_id ;
END

Now when you make an INSERT in subscriber_table:

insert into subscriber_table values (1, 1, 1);
insert into subscriber_table values (1, 2, 1);
insert into subscriber_table values (1, 4, 1);

It'll increment the column count_subscriber in product_table:

mysql> select * from product_table;
+---------+------------+------------------+
| item_id | product_id | count_subscriber |
+---------+------------+------------------+
|       1 |          1 |                3 |
+---------+------------+------------------+
1 rows in set (0.00 sec)

Best Regards

Max.