Delete

Translation reference for Transact-SQL Delete statement to Snowflake

Applies to

• SQL Server

• Azure Synapse Analytics

Some parts in the output code are omitted for clarity reasons.

Description

Removes one or more rows from a table or view in SQL Server. For more information regarding SQL Server Delete, check here.

[ WITH <common_table_expression> [ ,...n ] ]  
DELETE   
    [ TOP ( expression ) [ PERCENT ] ]   
    [ FROM ]   
    { { table_alias  
      | <object>   
      | rowset_function_limited   
      [ WITH ( table_hint_limited [ ...n ] ) ] }   
      | @table_variable  
    }  
    [ <OUTPUT Clause> ]  
    [ FROM table_source [ ,...n ] ]   
    [ WHERE { <search_condition>   
            | { [ CURRENT OF   
                   { { [ GLOBAL ] cursor_name }   
                       | cursor_variable_name   
                   }   
                ]  
              }  
            }   
    ]   
    [ OPTION ( <Query Hint> [ ,...n ] ) ]   
[; ]  
  
<object> ::=  
{   
    [ server_name.database_name.schema_name.   
      | database_name. [ schema_name ] .   
      | schema_name.  
    ]  
    table_or_view_name   
}  

Sample Source Patterns

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

IN -> SqlServer_01.sql

DELETE T1 FROM TABLE2 T2, TABLE1 T1 WHERE T1.ID = T2.ID

Snowflake

OUT -> SqlServer_01.sql

DELETE FROM
TABLE1 T1
USING TABLE2 T2
WHERE
T1.ID = T2.ID;

Note that, since the original DELETE was for T1, the presence of TABLE2 T2 in the FROM clause requires the creation of the USING clause.

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.

DELETE T
FROM
(
SELECT *
, DupRank = ROW_NUMBER() OVER (
              PARTITION BY key_value
              ORDER BY ( {expression} )
            )
FROM original_table
) AS T
WHERE DupRank > 1 

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

Insert duplicates

create table duplicatedRows(
    someID int,
    col2 bit,
    col3 bit,
    col4 bit,
    col5 bit
);

insert into duplicatedRows VALUES(10, 1, 0, 0, 1);
insert into duplicatedRows VALUES(10, 1, 0, 0, 1);
insert into duplicatedRows VALUES(11, 1, 1, 0, 1);
insert into duplicatedRows VALUES(12, 0, 0, 1, 1);
insert into duplicatedRows VALUES(12, 0, 0, 1, 1);
insert into duplicatedRows VALUES(13, 1, 0, 1, 0);
insert into duplicatedRows VALUES(14, 1, 0, 1, 0);
insert into duplicatedRows VALUES(14, 1, 0, 1, 0);

select * from duplicatedRows;

Output

someID  | col2  | col3  | col4  | col5 |

 10     | true  | false | false | true  |
 10     | true  | false | false | true  |
 11     | true  | true  | false | true  |
 12     | false | false | true  | true  |
 12     | false | false | true  | true  |
 13     | true  | false | true  | false |
 14     | true  | false | true  | false |
 14     | true  | false | true  | false |

Remove duplicated rows

Remove duplicates

DELETE f FROM (
	select  someID, row_number() over (
		partition by someID, col2
		order by
			case when COL3 = 1 then 1 else 0 end
			+ case when col4 = 1 then 1 else 0 end
			+ case when col5 = 1 then 1 else 0 end
			asc
		) as rownum
	from
		duplicatedRows
	) f where f.rownum > 1;
	
select * from duplicatedRows;

Output

someID  | col2  | col3  | col4  | col5 |

 10     | true  | false | false | true  |
 11     | true  | true  | false | true  |
 12     | false | false | true  | true  |
 13     | true  | false | true  | false |
 14     | true  | false | true  | false |

Snowflake

Create a table with duplicated rows

Insert duplicates

create table duplicatedRows(
    someID int,
    col2 BOOLEAN,
    col3 BOOLEAN,
    col4 BOOLEAN,
    col5 BOOLEAN
);

insert into duplicatedRows VALUES(10, 1, 0, 0, 1);
insert into duplicatedRows VALUES(10, 1, 0, 0, 1);
insert into duplicatedRows VALUES(11, 1, 1, 0, 1);
insert into duplicatedRows VALUES(12, 0, 0, 1, 1);
insert into duplicatedRows VALUES(12, 0, 0, 1, 1);
insert into duplicatedRows VALUES(13, 1, 0, 1, 0);
insert into duplicatedRows VALUES(14, 1, 0, 1, 0);
insert into duplicatedRows VALUES(14, 1, 0, 1, 0);

select * from duplicatedRows;

Output

someID  | col2  | col3  | col4  | col5 |

 10     | true  | false | false | true  |
 10     | true  | false | false | true  |
 11     | true  | true  | false | true  |
 12     | false | false | true  | true  |
 12     | false | false | true  | true  |
 13     | true  | false | true  | false |
 14     | true  | false | true  | false |
 14     | true  | false | true  | false |

Remove duplicated rows

Remove duplicates

  insert overwrite into duplicatedRows
            SELECT
                *
            FROM
                duplicatedRows
            QUALIFY
                ROW_NUMBER()
                over
                (partition by someID, col2
		    order by
			case when COL3 = 1 then 1 else 0 end
			+ case when col4 = 1 then 1 else 0 end
			+ case when col5 = 1 then 1 else 0 end
			asc) = 1;
	
select * from duplicatedRows;

Output

someID  | col2  | col3  | col4  | col5 |

 10     | true  | false | false | true  |
 11     | true  | true  | false | true  |
 12     | false | false | true  | true  |
 13     | true  | false | true  | false |
 14     | true  | false | true  | false |

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.

Known Issues

No issues were found.

Related EWIs

No related EWIs.