Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate Microsoft Fabric, Power BI, SQL, and AI community-led event. March 31 to April 2, 2025.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to: Databricks SQL Databricks Runtime
Deletes the rows that match a predicate. When no predicate is provided, deletes all rows.
This statement is only supported for Delta Lake tables.
DELETE FROM table_name [table_alias] [WHERE predicate]
Identifies an existing table. The name must not include a temporal specification.
table_name
must not be a foreign table.
Define an alias for the table. The alias must not include a column list.
Filter rows by predicate.
The WHERE
predicate supports subqueries, including IN
, NOT IN
, EXISTS
, NOT EXISTS
, and scalar subqueries. The following types of subqueries are not supported:
NOT IN
subquery inside an OR
, for example, a = 3 OR b NOT IN (SELECT c from t)
In most cases, you can rewrite NOT IN
subqueries using NOT EXISTS
. We recommend using
NOT EXISTS
whenever possible, as DELETE
with NOT IN
subqueries can be slow.
> DELETE FROM events WHERE date < '2017-01-01'
> DELETE FROM all_events
WHERE session_time < (SELECT min(session_time) FROM good_events)
> DELETE FROM orders AS t1
WHERE EXISTS (SELECT oid FROM returned_orders WHERE t1.oid = oid)
> DELETE FROM events
WHERE category NOT IN (SELECT category FROM events2 WHERE date > '2001-01-01')
Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate Microsoft Fabric, Power BI, SQL, and AI community-led event. March 31 to April 2, 2025.
Register todayTraining
Module
Work with Delta Lake tables in Microsoft Fabric - Training
Tables in a Microsoft Fabric lakehouse are based on the Delta Lake technology commonly used in Apache Spark. By using the enhanced capabilities of delta tables, you can create advanced analytics solutions.