SQL CREATE OR REPLACE VIEW Keyword
The SQL CREATE OR REPLACE VIEW statement is used to update a SQL VIEW. A SQL VIEW is a virtual table created based on the SQL statement. A view contains rows and columns just like a normal table. All SQL functions, WHERE, HAVING and JOINs statements can be used to create a SQL VIEW.
Syntax
The syntax for using CREATE OR REPLACE VIEW statement is given below:
CREATE OR REPLACE VIEW view_name AS SELECT column1, column2, column3, ... FROM table_name WHERE condition(s);
Example:
Consider a database table called Employee with the following records:
EmpID | Name | City | Age | Salary |
---|---|---|---|---|
1 | John | London | 25 | 3000 |
2 | Marry | New York | 24 | 2750 |
3 | Jo | Paris | 27 | 2800 |
4 | Kim | Amsterdam | 30 | 3100 |
5 | Ramesh | New Delhi | 28 | 3000 |
6 | Huang | Beijing | 28 | 2800 |
The below mentioned SQL statement is used to create a view on Employee table which contains all records of employee whose salary is greater than 2800.
CREATE VIEW [Employee Salary GT 2800] AS SELECT * FROM Employee WHERE Salary > 2800;
After creating the VIEW, it can be used as mentioned below:
SELECT * FROM [Employee Salary GT 2800];
This will produce the result as shown below:
EmpID | Name | City | Age | Salary |
---|---|---|---|---|
1 | John | London | 25 | 3000 |
4 | Kim | Amsterdam | 30 | 3100 |
5 | Ramesh | New Delhi | 28 | 3000 |
Here, the CREATE OR REPLACE VIEW statement can be used to update the above view. The below mentioned SQL statement is used to update the above view which contains all records of employee whose salary is greater than 2800 and age is greater than 25.
CREATE OR REPLACE VIEW [Employee Salary GT 2800] AS SELECT * FROM Employee WHERE Salary > 2800 AND AGE > 25;
After updating the VIEW, it contains:
SELECT * FROM [Employee Salary GT 2800];
This will produce the result as shown below:
EmpID | Name | City | Age | Salary |
---|---|---|---|---|
4 | Kim | Amsterdam | 30 | 3100 |
5 | Ramesh | New Delhi | 28 | 3000 |
❮ SQL Keywords