Dropping and altering views in SQL

Introduction

Views help us see data in a simple way. Sometimes, we need to change or remove these views to keep data clear and useful.

When a view shows old or wrong information and needs to be updated.

When you no longer need a view and want to clean up your database.

When you want to change how data is shown without changing the original tables.

When fixing mistakes made while creating a view.

When improving performance by changing a view's query.

Syntax

SQL

DROP VIEW view_name;

CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

DROP VIEW removes the view completely.

CREATE OR REPLACE VIEW changes the view's query to show different data.

Examples

This removes the view named employee_view from the database.

SQL

DROP VIEW employee_view;

This changes employee_view to show only active employees with their id, name, and department.

SQL

CREATE OR REPLACE VIEW employee_view AS
SELECT id, name, department
FROM employees
WHERE active = 1;

Sample Program

First, we create a view called product_view showing all products. Then, we change it to show only products costing more than 100. Next, we select all data from this view to see the filtered products. Finally, we remove the view.

SQL

CREATE VIEW product_view AS
SELECT id, name, price
FROM products;

CREATE OR REPLACE VIEW product_view AS
SELECT id, name, price
FROM products
WHERE price > 100;

SELECT * FROM product_view;

DROP VIEW product_view;

Important Notes

Not all database systems support ALTER VIEW. Sometimes you must drop and recreate the view.

Dropping a view does not delete the data in the original tables.

Be careful when dropping views used by other parts of your system.

Summary

DROP VIEW removes a view from the database.

CREATE OR REPLACE VIEW changes the query inside a view to update its data.

Use these commands to keep your views accurate and your database clean.