0

Here I am creating a view 

CREATE VIEW customerusage
AS
SELECT group_concat(customername SEPARATOR ' ||| '),
    customerlocation,
    customerid,
    product
FROM customer AS c
INNER JOIN product AS p ON (c.product_id = c.product_id)
GROUP BY product_id

This table is to be updated everyday, if it is done in every minute is too fine. How can I update it. Is any option other then view also fine to do.

MoonKnight
  • 23,214
  • 40
  • 145
  • 277
kumar
  • 2,905
  • 5
  • 22
  • 26
  • 4
    what do you mean by update? views are updated every time there is a change on the tables. – John Woo Sep 20 '13 at 11:03
  • every minute,the values in customer and product table are get vary,i need to track last minute data and store in view and it should be automated for every minute,is this possible? – kumar Sep 20 '13 at 11:09
  • 1
    I think you misunderstood what a `VIEW` is. You cannot store a value in a value. It is read-only virtual table and a projection of your `SELECT` statement. – John Woo Sep 20 '13 at 11:11
  • 1
    You don't need to update a view. It gets updated whenever there is a change in the underlying table. – heretolearn Sep 20 '13 at 11:11
  • can you please say ,is any other way to do this process in mysql ? – kumar Sep 20 '13 at 11:14
  • Check here to know [How a view works in MySQL](http://stackoverflow.com/questions/2878227/how-do-mysql-views-work) – heretolearn Sep 20 '13 at 11:16
  • If you are trying to update your view after the change in the table, then you don't have to do anything. If you are trying to do something else, please explain a bit more – heretolearn Sep 20 '13 at 11:38

1 Answers1

0

You need to make your view updatable. See http://dev.mysql.com/doc/refman/5.6/en/view-updatability.html

In summary:

A view is updatable if there is a one-to-one relationship between the rows in the view and the rows in the base table that are going to be updated.

A view is insertable if it is updatable and the columns present in the base table but not named in the view have default values.

I think, the expression group_concat(customername SEPARATOR ' ||| ') will break the one-to-one relationship. I.e. this expression could prevent your view to be updatable.

The expression GROUP BY product_id will definitely break a one-to-one relationship between the view and the base table.

The other way around, when you update/insert on the base table, it will be reflected on the view depending one-to-one relationship. If there is such a relationship, the view will use a MERGE algorithm and modifications in the base table will be reflected in the view. If there is no such a one-to-one relationship the used algorithm will be TEMPLATE, that means, a temporary table will be created for the view. See http://dev.mysql.com/doc/refman/5.0/en/view-algorithms.html for view algorithms.

Al_
  • 1,481
  • 1
  • 11
  • 22