CODEWITHSUNDEEP
azure-synapse
Patterson
Patterson

Reputation: 2761

How to Delete First Row of All Tables in Azure Synapse Serverless Pool

I have create a number of tables in Azure Synapse Analytics and I would like to remove the first row from each of the tables.

Can someone assist with code that will remove the first row from each table.

I tried the following:

DELETE TOP (1)
FROM   [dbo].[MyTable]

I got the error:

DML Operations are not supported with external tables.

Upvotes: 0

Views: 683

Answers (1)

Onur Omer
Onur Omer

Reputation: 526

External tables are read-only. They are just another abstraction layer to lake. You can't perform DML operations over those files from Synapse.

What you could do is:

  • Try to employ REJECT_TYPE and REJECT_VALUE in OPTIONS while creating the table.
  • Only use Serverless Views so that you can filter some data out
  • Implement a strategy to re-stage data to a distributed pool table by using CTAS and providing the filter there

General rule of thumb is: only get what you need, deletion is usually slow.

Best, Onur

Upvotes: 1

Related Questions