Online College Courses for Credit

+
CREATE OR REPLACE VIEW to Update Views

CREATE OR REPLACE VIEW to Update Views

Rating:
(0)
Author: Sophia Tutorial
Description:

Identify an instance where you would need to update a view.

(more)
See More
Fast, Free College Credit

Developing Effective Teams

Let's Ride
*No strings attached. This college course is 100% free and is worth 1 semester credit.

47 Sophia partners guarantee credit transfer.

299 Institutions have accepted or given pre-approval for credit transfer.

* The American Council on Education's College Credit Recommendation Service (ACE Credit®) has evaluated and recommended college credit for 33 of Sophia’s online courses. Many different colleges and universities consider ACE CREDIT recommendations in determining the applicability to their course and degree programs.

Tutorial

what's covered
This tutorial explores using the REPLACE VIEW or ALTER VIEW to make changes to views.

Some databases will not allow you to remove a column or add a column from a view. Rather, we would have to use the CREATE OR REPLACE VIEW command to make updates to changes to a view. It is important to note that if a view already exists, the columns must be named the same, with the same data types and in the same order that was created. With PostgreSQL, the database will allow you to add additional columns at the end of the list.

For example, let us create a view that has the invoice_id, total, and first and last name of the customer:

CREATE VIEW invoice_information
AS
SELECT invoice_id, total, first_name, last_name
FROM invoice
INNER JOIN customer ON invoice.customer_id = customer.customer_id;

To query the view, we can run the following SELECT statement:

SELECT *
FROM invoice_information;

File:11480-2440-1.png

If we intended to have the employee’s first and last name instead of the customer’s first and last name as we wanted to get the details about the employee, we could drop the view and then create it. However, if there were objects that depend on this view, we would have to drop those as well. Using the CREATE OR REPLACE VIEW will allow us to simply recreate it without having to get rid of the dependent objects.

Let us make the change in the view:

CREATE OR REPLACE VIEW invoice_information
AS
SELECT invoice_id, total, employee.first_name, employee.last_name
FROM invoice
INNER JOIN customer ON invoice.customer_id = customer.customer_id
INNER JOIN employee ON customer.support_rep_id = employee.employee_id;

Even though the names of the columns are the same and are in the same order, we will get this error:

File:11481-2440-2.png

Due to the first_name in the employee table being 20 characters, the data size does not match. We could in this case resize the employee table’s first_name column to 40 characters as well as the last_name:

ALTER TABLE employee
ALTER COLUMN first_name TYPE VARCHAR (40);
ALTER TABLE employee
ALTER COLUMN last_name TYPE VARCHAR (40);

Now if we attempt to make the modification again:

CREATE OR REPLACE VIEW invoice_information
AS
SELECT invoice_id, total, employee.first_name, employee.last_name
FROM invoice
INNER JOIN customer ON invoice.customer_id = customer.customer_id
INNER JOIN employee ON customer.support_rep_id = employee.employee_id;

We are now successful as the data type, column names, and order all match:

SELECT *
FROM invoice_information;

File:11482-2440-3.png

Since the data has changed, we may want to also change the name of the view. Rather than recreating it, we can use the ALTER VIEW statement to rename the view:

ALTER VIEW invoice_information RENAME TO invoice_employee_info;


try it
Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.

summary
The CREATE OR REPLACE VIEW allows you to replace an existing query as long as the column names, data types, data sizes and order of the columns matches.