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.
DELETE .. USING
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The USING clause allows for a JOIN between the table from which to delete rows with other tables.
Dialect support
This example using jOOQ:
deleteFrom(BOOK).using(AUTHOR).where(BOOK.AUTHOR_ID.eq(AUTHOR.ID)).and(AUTHOR.LAST_NAME.eq("Poe"))
Translates to the following dialect specific expressions:
ASE, Oracle, SQLServer, Sybase
DELETE FROM BOOK FROM AUTHOR WHERE ( BOOK.AUTHOR_ID = AUTHOR.ID AND AUTHOR.LAST_NAME = 'Poe' )
Aurora MySQL, MariaDB, MySQL
DELETE FROM BOOK USING BOOK, AUTHOR WHERE ( BOOK.AUTHOR_ID = AUTHOR.ID AND AUTHOR.LAST_NAME = 'Poe' )
Aurora Postgres, CockroachDB, DuckDB, Postgres, Redshift, Snowflake, YugabyteDB
DELETE FROM BOOK USING AUTHOR WHERE ( BOOK.AUTHOR_ID = AUTHOR.ID AND AUTHOR.LAST_NAME = 'Poe' )
BigQuery, Databricks, Hana, SQLDataWarehouse, SQLite, Trino, Vertica
DELETE FROM BOOK
WHERE BOOK.ID IN (
SELECT BOOK.ID
FROM BOOK, AUTHOR
WHERE (
BOOK.AUTHOR_ID = AUTHOR.ID
AND AUTHOR.LAST_NAME = 'Poe'
)
)
ClickHouse
DELETE FROM BOOK
WHERE ID IN (
SELECT BOOK.ID
FROM BOOK, AUTHOR
WHERE (
BOOK.AUTHOR_ID = AUTHOR.ID
AND AUTHOR.LAST_NAME = 'Poe'
)
)
DB2, Derby, Firebird, H2, HSQLDB
MERGE INTO BOOK USING AUTHOR ON ( BOOK.AUTHOR_ID = AUTHOR.ID AND AUTHOR.LAST_NAME = 'Poe' ) WHEN MATCHED THEN DELETE
Exasol
MERGE INTO BOOK USING AUTHOR ON ( BOOK.AUTHOR_ID = AUTHOR.ID AND AUTHOR.LAST_NAME = 'Poe' ) WHEN MATCHED THEN UPDATE SET BOOK.ID = BOOK.ID DELETE
MemSQL
DELETE BOOK FROM BOOK, AUTHOR WHERE ( BOOK.AUTHOR_ID = AUTHOR.ID AND AUTHOR.LAST_NAME = 'Poe' )
Teradata
DELETE BOOK FROM AUTHOR WHERE ( BOOK.AUTHOR_ID = AUTHOR.ID AND AUTHOR.LAST_NAME = 'Poe' )
Access, Informix
/* UNSUPPORTED */
Generated with jOOQ 3.21. Support in older jOOQ versions may differ. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!