Delete
Translation reference for Transact-SQL Delete statement to Snowflake
Description
Removes one or more rows from a table or view in SQL Server. For more information regarding SQL Server Delete, check here.
Sample Source Patterns
Sample Data
Basic Case
The transformation for the DELETE statement is fairly straightforward, with some caveats. One of these caveats is the way Snowflake supports multiple sources in the FROM clause, however, there is an equivalent in Snowflake as shown below.
SQL Server
Snowflake
Delete duplicates from a table
The following documentation explains a common pattern used to remove duplicated rows from a table in SQL Server. This approach uses the ROW_NUMBER
function to partition the data based on the key_value
which may be one or more columns separated by commas. Then, delete all records that received a row number value that is greater than 1. This value indicates that the records are duplicates. You can read the referenced documentation to understand the behavior of this method and recreate it.
The following example uses this approach to remove duplicates from a table and its equivalent in Snowflake. The transformation consists of performing an INSERT OVERWRITE statement which truncates the table (removes all data) and then inserts again the rows in the same table ignoring the duplicated ones. The output code is generated considering the same PARTITION BY
and ORDER BY
clauses used in the original code.
SQL Server
Create a table with duplicated rows
Remove duplicated rows
Snowflake
Create a table with duplicated rows
Remove duplicated rows
Consider that there may be several variations of this pattern, but all of them are based on the same principle and have the same structure.
Delete With INNER JOIN
SqlServer
Snowflake
Delete With LEFT JOIN
SqlServer
Snowflake
Delete With RIGHT JOIN
SqlServer
Snowflake
Known Issues
FULL JOIN not supported The FULL JOIN can not be represented using the (+) syntax. When found, SnowConvert will warn the user about this with an FDM.
Related EWIs
SSC-EWI-TS0081: Using a full join in a delete statement is not supported
Last updated