delete table from table in databricks

Question

delete table from table in databricks

Shambhu Rai 1,411

Hi Expert,

how to use delete table from table in databricks like below sql example

DELETE FROM Sales.SalesPersonQuotaHistory   
FROM Sales.SalesPersonQuotaHistory AS spqh  
INNER JOIN Sales.SalesPerson AS sp  
ON spqh.BusinessEntityID = sp.BusinessEntityID  
WHERE sp.SalesYTD > 2500000.00;

AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2023-09-20T10:15:26.8766667+00:00

Hi @Shambhu Rai ,

Just following up to see if the below answer helped. Please do consider clicking Accept Answer as accepted answers help community as well. Also, please click on Yes for the survey 'Was the answer helpful'

2 answers

Your answer

AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2023-09-20T10:15:26.8766667+00:00

Hi @Shambhu Rai ,

Just following up to see if the below answer helped. Please do consider clicking Accept Answer as accepted answers help community as well. Also, please click on Yes for the survey 'Was the answer helpful'

Answer 1

Marella, Lavanya 0

Hi @Shambhu Rai

Try something like this

%sql 
DELETE FROM Sales.SalesPersonQuotaHistory   
WHERE BusinessEntityID in (
  SELECT spqh.BusinessEntityID 
  FROM Sales.SalesPersonQuotaHistory AS spqh  
INNER JOIN Sales.SalesPerson AS sp  
ON spqh.BusinessEntityID = sp.BusinessEntityID  
WHERE sp.SalesYTD > 2500000.00)

Shambhu Rai 1,411

but what if it has 2 join conditions like below

DELETE FROM Sales.SalesPersonQuotaHistory   
WHERE BusinessEntityID in (
  SELECT spqh.BusinessEntityID 
  FROM Sales.SalesPersonQuotaHistory AS spqh  
INNER JOIN Sales.SalesPerson AS sp  
ON spqh.BusinessEntityID = sp.BusinessEntityID  and spqh.sourcesystem= sp.sourcesystem and spqh.BusinessEntityID = sp.BusinessEntityID  and spqh.date= sp.date
WHERE sp.SalesYTD > 2500000.00)

Answer 2

Hi @Shambhu Rai ,

Are you trying to look for solution for dataframe or on sql table?

Alternatively, you can try merge statement like below:

%sql
MERGE INTO Sales.SalesPersonQuotaHistory AS spqh
USING (
  SELECT sqh.*
  FROM Sales.SalesPersonQuotaHistory AS sqh
  JOIN Sales.SalesPerson AS sp
  ON sqh.BusinessEntityID = sp.BusinessEntityID
  WHERE sp.SalesYTD <= 2500000.00
) AS sq
ON spqh.BusinessEntityID = sq.BusinessEntityID
WHEN MATCHED THEN
  DELETE;

Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou

Share via

delete table from table in databricks

2 answers

Your answer