previous : next

Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11

OR REPLACE

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

Many dialects support a convenient OR REPLACE clause that allows for dropping any pre-existing view by the same name in a single statement. 

// Create a new view
create.createOrReplaceView("early_authors", "author_id", "first_name", "last_name")
      .as(select(AUTHOR.ID, AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
          .from(AUTHOR)

          // Any inserted or updated authors must continue to satisfy this condition
          .where(AUTHOR.ID.lt(200)))
      .execute();

Dialect support

This example using jOOQ:

createOrReplaceView("a", "id").as(select(AUTHOR.ID).from(AUTHOR))

Translates to the following dialect specific expressions:

ASE, Aurora Postgres, BigQuery, CockroachDB, DB2, DuckDB, Exasol, H2, Hana, MariaDB, MySQL, Oracle, Postgres, Vertica, YugabyteDB

CREATE OR REPLACE VIEW a(id)
AS
SELECT AUTHOR.ID
FROM AUTHOR

ClickHouse

CREATE OR REPLACE VIEW a
AS
SELECT t.id
FROM (
  SELECT AUTHOR.ID id
  FROM AUTHOR
) t

Firebird, SQLServer

CREATE OR ALTER VIEW a(id)
AS
SELECT AUTHOR.ID
FROM AUTHOR

Teradata

REPLACE VIEW a(id)
AS
SELECT AUTHOR.ID
FROM AUTHOR

Trino

CREATE OR REPLACE VIEW a
AS
SELECT t.id
FROM (
  SELECT AUTHOR.ID
  FROM AUTHOR
) t (id)

Access, Aurora MySQL, Derby, HSQLDB, Informix, MemSQL, Redshift, SQLDataWarehouse, SQLite, Snowflake, Sybase

/* UNSUPPORTED */

(These are currently generated with jOOQ 3.20, see #10141), or translate your own on our website


previous : next

Feedback

Do you have any feedback about this page? We'd love to hear it!

↑ Back to top

Community

Support

Legal

Documentation

The jOOQ Logo