SQL UPDATE VIEW

Updating and Creating view these are two main operations which can be performed on a SQL 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 main ways to update a view in SQL:

• Using the UPDATE statement: This method allows you to modify the data displayed in a view without changing its structure. It is generally applicable to simple views based on a single table.
• Using the CREATE OR REPLACE VIEW statement: This method is used when you need to redefine the structure or logic of an existing view. If the view doesn’t already exist, this command will create it; if it does, the existing view will be replaced with the new definition.

Syntax:

UPDATE view_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

To modify the structure of a view:

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

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.