0

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.

2 Answers 2

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.

1
  • thanks i was not really looking for a View based solution but i should look into it.
    – R T
    Dec 17, 2013 at 7:44
1

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.

1
  • i am gonn try that. thanks for your input.
    – R T
    Dec 17, 2013 at 7:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.