SQL UPDATE VIEW

Views in SQL are virtual tables created from one or more existing tables, providing a way to represent data in a structured format without physically storing it. Similar to tables, views require unique names within the database.

In this article, We will learn about the creation and updating of views and understand various examples in multiple methods and so on.

What is a View?

• The view is a virtual table created in the database.
• The view is created from a table or multiple tables.
• Views do not hold any data and do not exist physically in the database.
• Just like Table, the View name should be also unique in the database.

How to Create and Update 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.

Output:

Table Student

Let’s Create a View

The Syntax of the query for the creating View is as below.

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

So for the student table, we will create a view that, will have Roll_no , Marks and subject attributes from the Student Table.

CREATE VIEW view1 AS
SELECT Roll_no , Marks , subject
FROM Student;

Output:

view1

Explanation: In the above Query the view called view1 is created which will have the details of a student who has marks greater than 50.

SQL Update View

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

Let’s look at different ways to update the view

1. Updating View Using IN Operator

Here we will update the values of attributes only. So here we will update the view using the UPDATE keyword.

Example:

• The query for updating the view is as below

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

Output:

view1 (using IN Operator)

Explanation: Here we are updating the view1 where marks will be set to 50 for roll numbers 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.

Example:

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.

UPDATE view1 SET Marks = Marks*0.95;

Output:

view1 (using Arithmetic Operation)

Explanation: Here all marks will be updated to 0.95*Marks. The view will look like below.

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

Example:

The query for updating the view using the aggregate function is as below

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

Output:

view1 (using aggregate function)

Explanation: Here we are calculating the Total Marks scored in each subject and grouping them by subject name. So the updated view will be as below.

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.

Example:

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.

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

Output:

view1 (using subqueries)

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.