Available in versions: Dev (3.20) | Latest (3.19) | 3.18

This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.

ALTER VIEW .. AS

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

This statement allows for replacing the contents of an existing view.

Dialect support

This example using jOOQ:

alterView("v").as(select(one().as("a")))

Translates to the following dialect specific expressions:

Aurora Postgres, Postgres, YugabyteDB

DO $$
BEGIN
  DROP VIEW v;
  CREATE VIEW v
  AS
  SELECT 1 a;
END;
$$

DB2

BEGIN
  EXECUTE IMMEDIATE 'DROP VIEW v';
  EXECUTE IMMEDIATE 'CREATE VIEW v
  AS
  SELECT 1 a
  FROM SYSIBM.DUAL';
END

Firebird

EXECUTE BLOCK AS
BEGIN
  EXECUTE STATEMENT 'DROP VIEW v';
  EXECUTE STATEMENT 'CREATE VIEW v
  AS
  SELECT 1 a
  FROM RDB$DATABASE';
END

H2

CREATE ALIAS block_1709362445733_6474132 AS $$
  void x(Connection c) throws SQLException {
    try (PreparedStatement s = c.prepareStatement(
      "DROP VIEW v"
    )) {
      s.execute();
    }
    try (PreparedStatement s = c.prepareStatement(
      "CREATE VIEW v\n" +
      "AS\n" +
      "SELECT 1 a"
    )) {
      s.execute();
    }
  }
$$;
CALL block_1709362445733_6474132();
DROP ALIAS block_1709362445733_6474132;

Hana

DO BEGIN
  EXECUTE IMMEDIATE 'DROP VIEW v';
  EXECUTE IMMEDIATE 'CREATE VIEW v
  AS
  SELECT 1 a
  FROM SYS.DUMMY';
END;

MariaDB

BEGIN NOT ATOMIC
  DROP VIEW v;
  CREATE VIEW v
  AS
  SELECT 1 a;
END;

MySQL

CREATE PROCEDURE block_1709362453983_9213310()
MODIFIES SQL DATA
BEGIN
  DROP VIEW v;
  CREATE VIEW v
  AS
  SELECT 1 a;
END;
CALL block_1709362453983_9213310();
DROP PROCEDURE block_1709362453983_9213310;

Oracle

BEGIN
  EXECUTE IMMEDIATE 'DROP VIEW v';
  EXECUTE IMMEDIATE 'CREATE VIEW v
  AS
  SELECT 1 a';
END;

SQLDataWarehouse, Vertica

BEGIN
  DROP VIEW v;
  CREATE VIEW v
  AS
  SELECT 1 a;
END;

SQLServer

ALTER VIEW v
AS
SELECT 1 a

ASE, Access, Aurora MySQL, BigQuery, CockroachDB, Derby, DuckDB, Exasol, HSQLDB, Informix, MemSQL, Redshift, SQLite, Snowflake, Sybase, Teradata, Trino

/* UNSUPPORTED */

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

Feedback

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

The jOOQ Logo