UPDATE VIEW in SQL

In this tutorial, We will see how can we update the VIEW in SQL.

The SQL UPDATE VIEW command can be used to modify the data of a view.

 

All views are not updatable. So, UPDATE command is not applicable to all views. An updatable view is one which allows performing an UPDATE command on itself without affecting any other table.

SQL Synatx :

SQL> UPDATE < view_name >

         SET<column1>=<value1>,<column2>=<value2>,…..          WHERE <condition>;

 

What VIEWS can be Updated

  • The view is defined based on one and only one table.

  • The view must include the PRIMARY KEY of the table based upon which the view has been created.

  • The view should not have any field made out of aggregate functions.

  • The view must not have any DISTINCT clause in its definition.

  • The view must not have any GROUP BY or HAVING clause in its definition.

  • The view must not have any SUBQUERIES in its definitions.

 

UPDATE VIEW Example

First Let’s create a view and then update it. Create the view for those employees who are working in department 80.

 

SQL > CREATE VIEW emp_dept80

           AS Select * from employees 

           WHERE dept_id =80;

 

Now update the commission of all the employees in view.

SQL > Update emp_dept80

           SET Commission = .15;

 

0 Comment

Leave a Reply