SQL - UPDATE View



SQL UPDATE View Statement

A view is a database object that can contain rows (all or selected) from an existing table. It can be created from one or many tables which depends on the provided SQL query to create a view.

Unlike CREATE VIEW and DROP VIEW there is no direct statement to update the records of an existing view. We can use the SQL UPDATE Statement to modify the existing records in a table or a view.

Syntax

The basic syntax of the UPDATE query with a WHERE clause is as follows −

UPDATE view_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

You can combine N number of conditions using the AND or the OR operators.

Example

Assume we have created a table named CUSTOMERS using the CREATE TABLE statement using the following query −

CREATE TABLE CUSTOMERS(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25) ,
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Now, insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

Following query creates a view based on the above created table −

CREATE VIEW CUSTOMERS_VIEW AS SELECT * FROM CUSTOMERS;

You can verify the contents of a view using the SELECT query as shown below −

SELECT * FROM CUSTOMERS_VIEW;

The view will be displayed as follows −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 Hyderabad 4500.00
7 Muffy 24 Indore 10000.00

Following query updates the age of Ramesh to 35 in the above created CUSTOMERS_VIEW −

UPDATE CUSTOMERS_VIEW 
SET AGE = 35 WHERE name = 'Ramesh';

Verification

You can verify the contents of the CUSTOMERS_VIEW using the SELECT statement as follows −

SELECT * FROM CUSTOMERS_VIEW WHERE NAME ='Ramesh';

The resultant view would have the following record(s) −

ID NAME AGE ADDRESS SALARY
1 Ramesh 35 Ahmedabad 2000.00

Example

The following query will update the ADDRESS of a customer whose ID is 6 in the CUSTOMERS_VIEW.

UPDATE CUSTOMERS_VIEW SET ADDRESS = 'Pune' WHERE ID = 6;

Output

The query produces the following output −

Query OK, 1 row affected (0.21 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Verification

If you retrieve the record with ID value 6 using the SELECT statement as −

SELECT * FROM CUSTOMERS_VIEW WHERE ID=6;

The record returned would be −

ID NAME AGE ADDRESS SALARY
6 Komal 22 Hyderabad 4500.00

Updating Multiple Rows and Columns

Using UPDATE statement, multiple rows and columns in a view/table can also be updated. While updating multiple rows, specify the condition in a WHERE clause such that only required rows would satisfy it.

Example

Following query updates the NAME and AGE column values in the CUSTOMERS_VIEW of the record with ID value 3.

UPDATE CUSTOMERS_VIEW
SET NAME = 'Kaushik Ramanujan', AGE = 24
WHERE ID = 3;

Output

The query produces the following output −

Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Verification

You can verify whether the record is updated or not, using the following query −

SELECT * FROM CUSTOMERS_VIEW WHERE ID = 3;

The record returned would be −

ID NAME AGE ADDRESS SALARY
3 Kaushik Ramanujan 24 Kota 2000.00

Example

But if you want to modify/update the age values of all the records in the CUSTOMERS_VIEW, there is no need to use the WHERE clause.

UPDATE CUSTOMERS_VIEW SET AGE = AGE+6;

Output

This query produces the following output −

Query OK, 7 rows affected (0.10 sec)
Rows matched: 7  Changed: 7  Warnings: 0

Verification

To verify whether the records of the CUSTOMERS_VIEW are modified or not, use the following SELECT query −

SELECT * FROM CUSTOMERS_VIEW;

The resultant CUSTOMERS_VIEW would have the following records −

ID NAME AGE
1 Ramesh 41
2 Khilan 31
3 Kaushik Ramanujan 30
4 Chaitali 31
5 Hardik 33
6 Komal 28
7 Muffy 30
Advertisements