What is the Derby equivalent to CREATE OR REPLACE?

Question

Does anyone know the Derby equivalent for the following?

CREATE OR REPLACE VIEW myView AS SELECT ...

My internet searching hasn't found anything clear. Wondering if I may have to do something similar to what I had to do for MSSQL:

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'myView')
    DROP VIEW [dbo].[myView];

CREATE VIEW [dbo].[myView] AS SELECT ...

In which case I assume I'll have to head down this path.

See here: http://old.nabble.com/Create-table-if-not-exists--td22130625.html — ypercubeᵀᴹ, Jul 28 '11 at 00:22
and this: http://stackoverflow.com/questions/5866154/how-to-create-table-if-it-doesnt-exist-using-derby-db — ypercubeᵀᴹ, Jul 28 '11 at 00:23

score 3 · Accepted Answer · edited Sep 17 '14 at 18:33

3

It doesn't exists.

Just try to perform the SELECT and if it fails catch it with the exception code:

   try {
      // Try to perform your query on the view.
   } catch( SQLException e ) {
      // Compare exception code. If equals to X0Y32 then create the view.
   }

edited Sep 17 '14 at 18:33

blo0p3r

6,790
8
49
68

answered Jul 28 '11 at 01:05

Erre Efe

15,387
10
45
77

Unfortunately I'm not in a situation where I can try/catch using Java code. I really need to be able to do this purely in SQL. Is it possible to do something similar in SQL? – james.bunt Jul 28 '11 at 02:17
Nop. That syntax isn't standard is a language extension and isn't supported by Apache Derby. I think you should try your second approach then, sorry. – Erre Efe Jul 28 '11 at 02:25

What is the Derby equivalent to CREATE OR REPLACE?

1 Answers1