topic sql delete? in Data Engineering

sql delete?

dan11 — Sat, 05 Mar 2016 04:46:20 GMT

<pre> Hello databricks people, I started working with databricks today. I have a sql script which I developed with sqlite3 on a laptop. I want to port the script to databricks. I started with two sql statements: select count(prop_id) from prop0; delete from prop0 where prop_id is null; They seem like simple statements. When I run them on data bricks I see this: Unsupported language features in query: delete from prop0 where prop_id is null. I find it hard to believe that databricks does not support statement: delete from prop0 where prop_id is null Am I doing something wrong? Is it reasonable to expect that databricks should support: delete from prop0 where prop_id is null ?? </pre>

Re: sql delete?

dan11 — Sat, 05 Mar 2016 04:48:40 GMT

The js-editor in this forum is horrible; I tried using pre-tags because of the angle-brackets I see in the upper menu and they got sanitized.

Re: sql delete?

vida — Fri, 11 Mar 2016 17:56:56 GMT

Hi Dan,

Spark SQL is based on HiveQL. It allows you to use SQL syntax to do big data, like count your data. It does not, however, support operations like delete and update. I cover why in my talk here:

https://spark-summit.org/east-2016/events/not-your-fathers-database-how-to-use-apache-spark-properly-in-your-big-data-architecture/

-Vida

Re: sql delete?

Bill_Chambers — Fri, 11 Mar 2016 17:57:05 GMT

Hey Dan, good to hear you're getting started with Databricks. This is not a limitation of Databricks it's a restriction built into Spark itself. Spark is not a data store, it's a distributed computation framework. Therefore deleting data would be unnecessary. If you don't need it, you would just filter it out either in a query or by setting it up as a new table as below.

%sql SELECT * FROM prop0 where prop_id is null AS new_table

It's probably worth your time reading a bit more about the tools that Spark provides, the learning curve is steep but once you get past the first steps you'll start seeing the value! 🙂 I might recommend some of the material that we have in the community edition like some of the CS100 coursework.

Re: sql delete?

BaranitharanV — Tue, 19 Feb 2019 08:31:29 GMT

Vida, there is a document in Databricks menitoning that deletes are permitted on Delta.

https://docs.databricks.com/spark/latest/spark-sql/language-manual/delete.html

Are we missing anything here?

Re: sql delete?

oliverstonez — Wed, 25 Feb 2026 11:05:28 GMT

You aren't doing anything wrong logically, but Databricks requires row-level changes to happen on Delta Lake tables. Standard Spark tables (like those backed by raw Parquet) are often immutable. Have a look at the Language Manual for DELET E to ensure your table configuration matches the requirements for data manipulation.