SQL UPDATE View

Introduction

In this chapter, we will focus on the UPDATE operation in SQL views. While views provide a way to simplify complex queries and present data in a specific format, they can also be used to update data in the underlying tables, provided certain conditions are met. This chapter will cover the definition, syntax, and provide examples to help you understand how to update data through views effectively.

What is an UPDATE View?

An UPDATE view allows you to modify data in the underlying base tables through a view. Not all views are updatable; a view must meet specific criteria to allow updates. Generally, simple views that map directly to a single table without complex joins, aggregations, or functions can be updatable.

Syntax for UPDATE View

Basic Syntax

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

• view_name: The name of the view to be updated.
• column1, column2, ...: The columns to be updated.
• value1, value2, ...: The new values for the columns.
• condition: The condition to identify the rows to be updated.

Step-by-Step Example

Sample Tables

First, let's create two sample tables named employees and departments.

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    department_id INT,
    salary DECIMAL(10, 2),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

Insert Sample Data

INSERT INTO departments (department_id, department_name)
VALUES
(1, 'HR'),
(2, 'Finance'),
(3, 'IT');

INSERT INTO employees (first_name, last_name, email, department_id, salary)
VALUES
('Ramesh', 'Kumar', 'ramesh.kumar@example.com', 1, 50000),
('Sita', 'Patel', 'sita.patel@example.com', 2, 60000),
('Arjun', 'Singh', 'arjun.singh@example.com', 1, 55000),
('Priya', 'Sharma', 'priya.sharma@example.com', 2, 62000),
('Ravi', 'Verma', 'ravi.verma@example.com', NULL, 50000);

Creating a Simple View

To create a view that displays employee names and their salaries:

CREATE VIEW employee_salary_view AS
SELECT id, first_name, last_name, salary
FROM employees;

Updating Data Through the View

To update the salary of an employee through the view:

UPDATE employee_salary_view
SET salary = 65000
WHERE id = 1;

Verifying the Update

To verify that the update was successful, you can query the employees table:

SELECT * FROM employees;

Output

id	first_name	last_name	email	department_id	salary
1	Ramesh	Kumar	ramesh.kumar@example.com	1	65000
2	Sita	Patel	sita.patel@example.com	2	60000
3	Arjun	Singh	arjun.singh@example.com	1	55000
4	Priya	Sharma	priya.sharma@example.com	2	62000
5	Ravi	Verma	ravi.verma@example.com	NULL	50000

In this example, the salary of Ramesh Kumar was successfully updated through the view.

Creating a View with a Join (Non-Updatable)

To demonstrate a non-updatable view, let's create a view that joins employees and departments:

CREATE VIEW employee_department_view AS
SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
JOIN departments ON employees.department_id = departments.department_id;

Attempting to Update a Non-Updatable View

Attempting to update this view will result in an error:

UPDATE employee_department_view
SET department_name = 'Marketing'
WHERE first_name = 'Ramesh';

This command will result in an error because the view includes a join and is not directly updatable.

Conclusion

The UPDATE operation on views allows for modifying data in the underlying base tables through a view, provided the view meets certain criteria. This chapter covered the basic syntax for updating views, provided examples to illustrate its use, and demonstrated the limitations of updating complex views with joins. Understanding how to use UPDATE on views effectively will enhance your ability to manage and manipulate data in a relational database. In the next chapter, we will explore DROP or DELETE VIEW.