CTEs in DML Statements

Database	Status	Syntax Family	Min Version
PostgreSQL	full	with-dml	9.1
MySQL	full	with-dml	8.0
MariaDB	full	with-dml	10.2
Oracle	full	with-dml	-
SQL Server	full	with-dml	-
SQLite	full	with-dml	3.35.0
Redshift	full	sql-standard	-
DB2	full	sql-standard	-
Snowflake	full	sql-standard	-
BigQuery	full	sql-standard	-

Compare Databases

Database Details

PostgreSQL full

WITH new_data AS (
  SELECT id, email FROM import_staging
)
INSERT INTO users (id, email)
SELECT id, email FROM new_data;

Supports CTEs in INSERT, UPDATE, and DELETE.
Also supports writable CTEs: CTEs that contain DML with RETURNING.
Writable CTEs execute all DML branches within the same snapshot.

Docs: WITH Queries

MySQL full

WITH new_data AS (
  SELECT id, email FROM import_staging
)
INSERT INTO users (id, email)
SELECT id, email FROM new_data;

CTEs can be used in INSERT ... SELECT and REPLACE ... SELECT.
CTEs in UPDATE and DELETE are supported since MySQL 8.0.
No writable CTEs (CTE body cannot contain DML).

Docs: WITH (CTE)

MariaDB full

WITH new_data AS (
  SELECT id, email FROM import_staging
)
INSERT INTO users (id, email)
SELECT id, email FROM new_data;

CTEs in DML supported since MariaDB 10.2.
No writable CTEs.

Docs: WITH (CTE)

Oracle full

INSERT INTO users (id, email)
WITH new_data AS (
  SELECT id, email FROM import_staging
)
SELECT id, email FROM new_data;

CTEs can be used with INSERT, UPDATE, and DELETE.
Note: WITH clause comes after INSERT INTO ... in Oracle syntax.
No writable CTEs.

Docs: Subquery Factoring

SQL Server full

WITH new_data AS (
  SELECT id, email FROM import_staging
)
INSERT INTO users (id, email)
SELECT id, email FROM new_data;

CTEs supported in INSERT, UPDATE, and DELETE.
CTE can reference the target table for complex updates.

Docs: WITH common_table_expression

SQLite full

WITH new_data AS (
  SELECT id, email FROM import_staging
)
INSERT INTO users (id, email)
SELECT id, email FROM new_data;

CTEs in INSERT, UPDATE, and DELETE supported since 3.35.0.

Docs: WITH clause

Redshift full

WITH to_deactivate AS (
  SELECT id FROM users WHERE last_login < '2022-01-01'
)
DELETE FROM users
USING to_deactivate
WHERE users.id = to_deactivate.id;

CTEs (WITH clause) are supported in INSERT, UPDATE, and DELETE statements.

Docs: WITH clause

DB2 full

WITH inactive AS (
  SELECT id FROM users WHERE last_login < '2022-01-01'
)
DELETE FROM users
WHERE id IN (SELECT id FROM inactive);

CTEs in DML: WITH cte AS (...) INSERT/UPDATE/DELETE.

Docs: SELECT

Snowflake full

WITH to_deactivate AS (
  SELECT id FROM users WHERE last_login < '2022-01-01'
)
DELETE FROM users
WHERE id IN (SELECT id FROM to_deactivate);

CTEs supported in INSERT, UPDATE, DELETE, and MERGE.

Docs: WITH

BigQuery full

WITH to_deactivate AS (
  SELECT id FROM users WHERE last_login < TIMESTAMP '2022-01-01'
)
DELETE FROM users
WHERE id IN (SELECT id FROM to_deactivate);

CTEs supported in INSERT, UPDATE, DELETE, and MERGE DML statements.

Docs: WITH clause