40

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

Franck Dernoncourt
  • 77,520
  • 72
  • 342
  • 501
kumar kasimala
  • 759
  • 1
  • 6
  • 17

3 Answers3

35

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.

Bill the Lizard
  • 398,270
  • 210
  • 566
  • 880
Sachin R
  • 11,606
  • 10
  • 35
  • 40
  • This answers the question partially; How do you do that in H2? – Nicolas Feb 04 '11 at 18:08
  • 5
    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...) – obe Sep 26 '18 at 12:51
34

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

create or replace view YourView
as
select * from users
Andomar
  • 232,371
  • 49
  • 380
  • 404
  • 5
    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. – flymike Jul 10 '15 at 17:22
  • 1
    in SQL Server is `create or alter view YourView as ...` – sports Feb 12 '20 at 20:28
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 
);
Daniel
  • 69
  • 5
  • Is this this H2 database? https://www.h2database.com/html/commands.html#create_view I cannot see `IF NOT EXISTS` anywhere. – petrch Mar 17 '21 at 13:52
  • 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"** – Daniel Apr 05 '21 at 11:14
  • Oh, thanks, my fault, I clearly did a poor search. I am sorry. – petrch Apr 05 '21 at 19:47
  • 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. – ChrCury78 Jun 30 '21 at 14:39
  • Understand. This command i wrote is for H2 database. – Daniel Jul 08 '21 at 18:05