SQL UPDATE VIEW

SQL Views are virtual tables that represent the result of a SELECT query. They simplify complex queries and present data in a more understandable format. While views don't store data themselves, they provide a convenient way to access and manipulate data without modifying the underlying tables

In this article, we will explain how to create and update SQL views, including various techniques and examples, to help improve our understanding and optimize our database queries.

How to Create a View in SQL

Creating a view in SQL is straightforward. The CREATE VIEW command is used to define a view. The view can be based on a simple or complex query. This SQL command defines a view by selecting specific columns from a table or joining multiple tables based on certain conditions.

Syntax

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

How to Update a View in SQL

Updating a view in SQL means modifying the data it shows. However, not all views can be updated instantly. Views with complex queries, like those with joins, subqueries, or aggregate functions, may not be directly updatable.

There are two ways to update a view: using the UPDATE keyword or using the CREATE OR REPLACE keyword

• The UPDATE keyword is used to update the view without changing the schema of the table. Update keyword will be used to update the values of the attributes.
  
• CREATE OR REPLACE keyword is used to update the view where the schema of the the view can also be changed. As the name suggests, if the view is not present it will get created. If view is present then view will get Replaced

Examples of Updating a View

To understand How to Create and Update Views we need a table on which we will perform various operations and queries. Here we will consider a table called Student which contains Roll_No , name, Marks , and Subject as Columns.

1. Updating View Using IN Operator

This method allows updating the values in the view using the UPDATE keyword. We will set specific conditions to update data in the view. The query for updating the view is as below

Query:

UPDATE view1  
SET Marks=50 
where Roll_no in (3,5);

Output

Explanation:

This query updates the view1 where the Marks will be set to 50 for the students with Roll_No 3 and 5

2. Updating View Using Arithmetic Operation

We can update the view using arithmetic operations also. Arithmetic operations are used to change the values of attributes present in the view. Here we are updating the view. Marks will be updated using the Marks*0.95 formula. So query for the given condition will be as below.

Query:

UPDATE view1 SET Marks = Marks*0.95;

Output

Explanation:

In this query, the Marks for all students in view1 will be updated to 95% of their current values, effectively applying a scaling factor.

3. Updating View Using Aggregate Function

We can also update the view using the aggregate function. As we are using the aggregate function, we need to change the schema/ structure of the view. So we will CREATE OR REPLACE VIEW to update the view. The query for updating the view using the aggregate function is as below

Query:

CREATE OR REPLACE VIEW view1 AS
SELECT Subject, SUM(Marks) AS TotalMarks
FROM Student
GROUP BY Subject;

Output

Explanation:

This query creates or replaces view1 with a new definition that calculates the total marks (SUM(Marks)) for each subject. The view will now display total marks grouped by subject.

4. Updating View Using Subqueries

The view can be updated using nestes or subqueries. When there is a need for more than a query for updating the view, we can update the view. Here we are using the CREATE OR REPLACE keyword as we need to change the structure of view. The query for updating the view which is using subqueries is as below.

Query:

CREATE OR REPLACE VIEW view1 AS
SELECT Name,
       (SELECT SUM(Marks) FROM Student s WHERE s.Subject = Student.Subject) AS TotalMarks
FROM Student;

Output

Explanation:

Here we created the new attribute TotalMarks which will have a sum of marks of the respective subject and the marks will get assigned to the respective student of that subject. for example, Kartik has the subject math so he will get assigned the sum of scores of all student who has math subject i.e. 170.

Advantages Of View

1. Data Access: View helps to restrict the data access and can be used to display the required information from the table without creating a a new table
   
2. Storage Capacity: View takes very little space to store data.
   
3. Usability: View is used to present the data in Proper format as compared to the data present in the tables.
   
4. Consistency: A view represents the database's consistent structure and if the original tables are restructured or changed.
   
5. Query simplicity: Data can be easily presented in structured format from various tables into a single table only.

Conclusion

In conclusion, views in SQL offer a flexible means of manipulating and presenting data. By creating views, users can easily access and display specific data subsets without modifying the original tables. This article has demonstrated how to create views and update them using various techniques, such as the UPDATE keyword, arithmetic operations, aggregate functions, and subqueries. Understanding and effectively using views can greatly enhance database management and query simplicity.