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.

Solution

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