Sql updating a view

When modifying data through a view (that is, using INSERT or UPDATE statements) certain limitations exist depending upon the type of view.

Views that access multiple tables can only modify one of the tables in the view.

, that is to say can the engine uniquely identify the row(s) to be updated and secondly are the fields updateable.

If your view has a calculated field or represents the product of a parent/child join then the default answer is probably no. in MS SQL Server and Oracle (to take just two examples) you can have triggers that fire when you attempt to insert or update a view such that you can make something that the server doesn't think updateable into something that is - usually because you have knowledge that the server can't easily infer from the schema.

To create a view, a user must have the appropriate system privilege according to the specific implementation.

The basic CREATE VIEW syntax is as follows − ---- ---------- ----- ----------- ---------- | ID | NAME | AGE | ADDRESS | SALARY | ---- ---------- ----- ----------- ---------- | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | ---- ---------- ----- ----------- ---------- The WITH CHECK OPTION is a CREATE VIEW statement option.

The view is, in essence, a stored SELECT statement that masquerades as a table.A view can contain all rows of a table or select rows from a table.A view can be created from one or many tables which depends on the written SQL query to create a view.The purpose of the WITH CHECK OPTION is to ensure that all UPDATE and INSERTs satisfy the condition(s) in the view definition.If they do not satisfy the condition(s), the UPDATE or INSERT returns an error.

Leave a Reply