create if not exists view?

Question

Is there any way to create view if not exists in MySQL or H2 Database?

Solution 1

From section 12.1.12. CREATE VIEW Syntax of the MySQL 5.0 Reference Manual:

CREATE VIEW Syntax

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

The CREATE VIEW statement creates a new view, or replaces an existing one if the OR REPLACE clause is given. This statement was added in MySQL 5.0.1. If the view does not exist, CREATE OR REPLACE VIEW is the same as CREATE VIEW. If the view does exist, CREATE OR REPLACE VIEW is the same as ALTER VIEW.

Solution 2

The usual way is to overwrite a view using create or replace:

create or replace view YourView
as
select * from users

Solution 3

On H2 you can add IF NOT EXISTS before the view name you want to create. e.g.:

CREATE VIEW IF NOT EXISTS viewExampleName (column1, column2) 
AS ( 
    SELECT column1, column2
    FROM example_table 
); 

Comments

[Nicolas]

This answers the question partially; How do you do that in H2?

[flymike]

But, if an existing view is replaced, don't its dependent views become invalid? That's why there's a need to test for the existence of a view.

[obe]

The result of using CREATE OR REPLACE is NOT the same as CREATE IF NOT EXISTS. The former would overwrite an existing view, whereas the latter would not (if it were available in MySQL...)

[sports]

in SQL Server is create or alter view YourView as ...

[petrch]

Is this this H2 database? h2database.com/html/commands.html#create_view I cannot see IF NOT EXISTS anywhere.

[Daniel]

Yes, it is for H2. On the link you just posted you can see the "IF NOT EXISTS" on the image under the title "CREATE VIEW"

[petrch]

Oh, thanks, my fault, I clearly did a poor search. I am sorry.

[ChrCury78]

The server I am using (probably old one) is MySQL version 5.5 does not recognize "IF NOT EXISTS" but "CREATE OR REPLACE VIEW .. " worked fine.

[Daniel]

Understand. This command i wrote is for H2 database.