CODEWITHSUNDEEP
databricksazure-databricksdelta-lake
Tony
Tony

Reputation: 311

how to delete the data from the Delta Table?

I was actually trying to delete the data from the Delta table.

When i run the below query, I'm getting data around 500 or 1000 records.

SELECT * FROM table1 inv 
join (SELECT col1, col2, col2, min(Date) minDate, max(Date) maxDate FROM table2 a GROUP BY col1, col2, col3) aux
on aux.col1 = inv.col1 and aux.col2 = inv.col2 and aux.col3 = inv.col3 
WHERE Date between aux.minDate and aux.maxDate

But when i try to delete that 500 records with the below query I'm getting error with syntax.

DELETE FROM table1 inv 
join (SELECT col1, col2, col2, min(Date) minDate, max(Date) maxDate FROM table2 a GROUP BY col1, col2, col3) aux
on aux.col1 = inv.col1 and aux.col2 = inv.col2 and aux.col3 = inv.col3 
WHERE Date between aux.minDate and aux.maxDate

Please someone help me here.

Thanks in advance :).

Upvotes: 0

Views: 3610

Answers (1)

gatear
gatear

Reputation: 946

Here is the sql reference:

DELETE FROM table_identifier [AS alias] [WHERE predicate]

You can't use JOIN here, so expand your where clause according to your needs.
Here are some examples:

DELETE FROM table1
  WHERE EXISTS (SELECT ... FROM table2 ...)

DELETE FROM table1
  WHERE table1.col1 IN (SELECT ... FROM table2 WHERE ...)

DELETE FROM table1
  WHERE table1.col1 NOT IN (SELECT ... FROM table2 WHERE ...)

Upvotes: 2

Related Questions