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

Sample Data

SqlServer

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DepartmentID INT
);

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(50)
);

INSERT INTO Employees (EmployeeID, FirstName, LastName, DepartmentID) VALUES
(1, 'John', 'Doe', 1),
(2, 'Jane', 'Smith', 2),
(3, 'Bob', 'Johnson', 1),
(4, 'Alice', 'Brown', 3),
(5, 'Michael', 'Davis', NULL);

INSERT INTO Departments (DepartmentID, DepartmentName) VALUES
(1, 'Sales'),
(2, 'Marketing'),
(3, 'Engineering'),
(4, 'Finance');

Snowflake

CREATE OR REPLACE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DepartmentID INT
);

CREATE OR REPLACE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(50)
);

INSERT INTO Employees (EmployeeID, FirstName, LastName, DepartmentID) VALUES
(1, 'John', 'Doe', 1),
(2, 'Jane', 'Smith', 2),
(3, 'Bob', 'Johnson', 1),
(4, 'Alice', 'Brown', 3),
(5, 'Michael', 'Davis', NULL);

INSERT INTO Departments (DepartmentID, DepartmentName) VALUES
(1, 'Sales'),
(2, 'Marketing'),
(3, 'Engineering'),
(4, 'Finance');

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

IN -> SqlServer_01.sql

DELETE T1 FROM Departments T2, Employees T1 WHERE T1.DepartmentID = T2.DepartmentID

Snowflake

OUT -> SqlServer_01.sql

DELETE FROM
Employees T1
USING Departments T2
WHERE
T1.DepartmentID = T2.DepartmentID;

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.

Delete With INNER JOIN

SqlServer

Input

DELETE ee
FROM Employees ee INNER JOIN Departments dept
ON ee.DepartmentID = dept.DepartmentID;

SELECT * FROM Employees;

Output

+----------+---------+--------+------------+
|EmployeeID|FirstName|LastName|DepartmentID|
+----------+---------+--------+------------+
|5         |Michael  |Davis   |null        |
|6         |Lucas    |Parker  |8           |
+----------+---------+--------+------------+

Snowflake

Input

DELETE FROM
    Employees ee
USING Departments dept
WHERE
    ee.DepartmentID = dept.DepartmentID;

SELECT
    *
FROM
    Employees;

Output

+----------+---------+--------+------------+
|EmployeeID|FirstName|LastName|DepartmentID|
+----------+---------+--------+------------+
|5         |Michael  |Davis   |null        |
|6         |Lucas    |Parker  |8           |
+----------+---------+--------+------------+

Delete With LEFT JOIN

SqlServer

Input

DELETE Employees
FROM Employees LEFT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID
WHERE Departments.DepartmentID IS NULL;

SELECT * FROM Employees;

Output

+----------+---------+--------+------------+
|EmployeeID|FirstName|LastName|DepartmentID|
+----------+---------+--------+------------+
|1         |John     |Doe     |1           |
|2         |Jane     |Smith   |2           |
|3         |Bob      |Johnson |1           |
|4         |Alice    |Brown   |3           |
+----------+---------+--------+------------+

Snowflake

Input

DELETE FROM
    Employees
USING Departments
WHERE
    Departments.DepartmentID IS NULL
    AND Employees.DepartmentID = Departments.DepartmentID(+);

SELECT
    *
FROM
    Employees;

Output

+----------+---------+--------+------------+
|EmployeeID|FirstName|LastName|DepartmentID|
+----------+---------+--------+------------+
|1         |John     |Doe     |1           |
|2         |Jane     |Smith   |2           |
|3         |Bob      |Johnson |1           |
|4         |Alice    |Brown   |3           |
+----------+---------+--------+------------+

Delete With RIGHT JOIN

SqlServer

Input

DELETE Employees
FROM Employees RIGHT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID
WHERE Employees.DepartmentID IS NOT NULL;

SELECT * FROM Employees;

Output

+----------+---------+--------+------------+
|EmployeeID|FirstName|LastName|DepartmentID|
+----------+---------+--------+------------+
|5         |Michael  |Davis   |null        |
|6         |Lucas    |Parker  |8           |
+----------+---------+--------+------------+

Snowflake

Input

DELETE FROM
    Employees
USING Departments
WHERE
    Employees.DepartmentID IS NOT NULL
    AND Employees.DepartmentID(+) = Departments.DepartmentID;

SELECT
    *
FROM
    Employees;

Output

+----------+---------+--------+------------+
|EmployeeID|FirstName|LastName|DepartmentID|
+----------+---------+--------+------------+
|5         |Michael  |Davis   |null        |
|6         |Lucas    |Parker  |8           |
+----------+---------+--------+------------+

Known Issues

1. 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.

Input

DELETE Employees
FROM Employees FULL OUTER JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID
WHERE Departments.DepartmentID IS NULL;

Output

!!!RESOLVE EWI!!! /*** SSC-EWI-TS0081 - USING A FULL JOIN IN A DELETE STATEMENT IS NOT SUPPORTED ***/!!!
DELETE FROM
    Employees
USING Departments
WHERE
    Departments.DepartmentID IS NULL
    AND Employees.DepartmentID = Departments.DepartmentID;

Related EWIs

1. SSC-EWI-TS0081: Using a full join in a delete statement is not supported