SQL - UPDATE View

---

The SQL UPDATE Query is used to modify the existing records in a table or a view. It is a Data Manipulation Language Command as it only modifies the data of the database object.

Since it only interacts with the data of a table or a view, the UPDATE statement needs to used cautiously. If the data to be modified aren't selected beforehand, all the rows in the table associated with the view will be affected so the correct data will either be lost or needs to be reinserted. Therefore, to filter records that need to be modified, you can use a WHERE clause. Using a WHERE clause, you can either update a single row or multiple rows.

The UPDATE statement makes use of locks on each row while modifying them in a table or view, and once the row is modified, the lock is released. Therefore, it can either make changes to a single row or multiple rows with a single query.

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. A view in a database can be updated under certain conditions which are given below −

• The SELECT clause may not contain the keyword DISTINCT.

• The SELECT clause may not contain summary functions.

• The SELECT clause may not contain set functions.

• The SELECT clause may not contain set operators.

• The SELECT clause may not contain an ORDER BY clause.

• The FROM clause may not contain multiple tables.

• The WHERE clause may not contain subqueries.

• The query may not contain GROUP BY or HAVING.

• Calculated columns may not be updated.

• All NOT NULL columns from the base table must be included in the view in order for the INSERT query to function.

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 (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Komal', 22, 'MP', 4500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (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;
+----+----------+-----+-----------+----------+
| 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 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Now, through the view you created, you can update the age of Ramesh to 35 in the original CUSTOMERS table, using the following code block −

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

This would ultimately update the base table CUSTOMERS and the same would reflect in the view itself.

Verification

Now, try to query the base table using the SELECT statement as follows −

SELECT * FROM CUSTOMERS;

Now, CUSTOMERS table would have the following records −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  35 | 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 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Example

Consider the updated CUSTOMERS table having the following records −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  35 | 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 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

The following query will update the ADDRESS for a customer whose ID number is 6 in the table.

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

Output

The query produces the following output −

(1 row affected)

Verification

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

SELECT * FROM CUSTOMERS;

Now, the CUSTOMERS table would have the following records −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  35 | 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 | Pune      |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Updating Multiple Rows and Columns

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

However, to update multiple columns, set the new values to all the columns that need to be updated. In this case, using the WHERE clause would narrow down the records of the table and not using the clause would change all the values in these columns.

Syntax

Following is the syntax to update multiple rows and columns −

UPDATE table_name
SET column_name1 = new_value, column_name2 = new_value...
WHERE condition(s)

Example

If you want to modify the NAME and AGE column values in the CUSTOMERS table for WHERE ID = 3, use the following code block.

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

Output

The query produces the following output −

(1 row affected)

Verification

To verify whether the records of the CUSTOMERS table are modified or not through the VIEW, use the following SELECT query below −

SELECT * FROM CUSTOMERS;

Now, CUSTOMERS table would have the following records −

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

Example

But if you want to modify all records of the AGE column in the CUSTOMERS table, you need not specify a condition in the WHERE clause as shown in the following query −

UPDATE CUSTOMERS_VIEW
SET AGE = 24;

Output

This query produces the following output −

(7 rows affected)

Verification

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

SELECT * FROM CUSTOMERS;

Now, CUSTOMERS table would have the following records −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  24 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  24 | Delhi     |  1500.00 |
|  3 | Kaushik  |  24 | Kota      |  2000.00 |
|  4 | Chaitali |  24 | Mumbai    |  6500.00 |
|  5 | Hardik   |  24 | Bhopal    |  8500.00 |
|  6 | Komal    |  24 | Pune      |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+