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

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.