CODEWITHSUNDEEP
snowflake-cloud-data-platform
Ravi
Ravi

Reputation: 19

How to DELETE top N records from Table in Snowflake Database

I want to perform DELETE operation with record limit in Snowflake DB, my requirement is to perform DELETE operation in batches like first delete top 1000 records then next 1000 records and so on.

In SQL Server we can use query like below:

DELETE TOP (1000) FROM TABLE_NAME;

I am looking for similar query for Snowflake DB, I went through Snowflake Documentation for Delete - https://docs.snowflake.com/en/sql-reference/sql/delete.html, but I didn't find matching syntax with TOP OR LIMIT.

Upvotes: 1

Views: 7281

Answers (2)

Mike Walton
Mike Walton

Reputation: 7339

This might not be the answer you are looking for, but the reason you are deleting in chunks of 1000 on SQL Server is because SQL Server logs those into transaction logs, deletes the records, updates indexes, etc. and it is more efficient to do things that way. In Snowflake, it is not.

Snowflake doesn't log deletes, doesn't have indexes, and in fact, doesn't actually delete records, it instead recreates the micropartitions that those records are coming from without the records being deleted. So, in fact, it is actually far less efficient to delete in smaller batches in Snowflake. You should instead, simply issue a single delete statement with the records you wish to delete and let it do its thing.

Upvotes: 3

hopeIsTheonlyWeapon
hopeIsTheonlyWeapon

Reputation: 567

Try this


DELETE FROM  <TABLE>
WHERE <ID_COL> IN
(
SELECT TOP 2 <ID_COL> FROM <TABLE>);

Upvotes: 3

Related Questions