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_1733921655476_8284040 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_1733921655476_8284040(); DROP ALIAS block_1733921655476_8284040;
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_1733921661998_3502190() MODIFIES SQL DATA BEGIN DROP VIEW v; CREATE VIEW v AS SELECT 1 a; END; CALL block_1733921661998_3502190(); DROP PROCEDURE block_1733921661998_3502190;
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, ClickHouse, CockroachDB, Derby, DuckDB, Exasol, HSQLDB, Informix, MemSQL, Redshift, SQLite, Snowflake, Sybase, Teradata, Trino
/* UNSUPPORTED */
Generated with jOOQ 3.20. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!