SnowflakeDocumentation Home

DELETE

Description

Deletes rows from tables. (Redshift SQL Language Reference Delete Statement).

This syntax is fully supported in Snowflake.

Grammar Syntax

[ WITH [RECURSIVE] common_table_expression [, common_table_expression , ...] ]
DELETE [ FROM ] { table_name | materialized_view_name }
    [ USING table_name, ... ]
    [ WHERE condition ]

Sample Source Patterns

Setup data

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    department VARCHAR(255),
    manager_id INT REFERENCES employees(id)
);

INSERT INTO employees (id, name, department, manager_id) VALUES
(1, 'Alice', 'Sales', 2),
(2, 'Bob', 'Sales', 1),
(3, 'Charlie', 'Sales', 1),
(4, 'David', 'Marketing', 2),
(5, 'Eve', 'Marketing', 4),
(6, 'Frank', 'Marketing', 4),
(7, 'Grace', 'Engineering', 6),
(8, 'Helen', 'Engineering', 7),
(9, 'Ivy', 'Engineering', 7),
(10, 'John', 'Sales', 3),
(11, 'Joe', 'Engineering', 5);


CREATE TABLE departments (
    department_name VARCHAR(255)
);

INSERT INTO departments (department_name) VALUES
('Sales'), 
('Marketing'), 
('Engineering');

From Clause

Update a table by referencing information from other tables. In Redshift, the FROM keyword is optional, but in Snowflake, it is mandatory. Therefore, it will be added in cases where it's missing.

Input Code:

IN -> Redshift_01.sql
DELETE employees;

SELECT * FROM employees ORDER BY id;

Output Code:

OUT -> Redshift_01.sql
DELETE FROM
    employees;
    
SELECT * FROM employees ORDER BY id;

Where Clause

Restricts updates to rows that match a condition. When the condition returns true, the specified SET columns are updated. The condition can be a simple predicate on a column or a condition based on the result of a subquery. This clause is fully equivalent in Snowflake.

Input Code:

IN -> Redshift_02.sql
DELETE FROM employees
WHERE department = 'Marketing';

SELECT * FROM employees
ORDER BY id;

Output Code:

OUT -> Redshift_02.sql
DELETE FROM
    employees
WHERE department = 'Marketing';

SELECT * FROM
    employees
ORDER BY id;

Using Clause

This clause introduces a list of tables when additional tables are referenced in the WHERE clause condition. This clause is fully equivalent in Snowflake.

Input Code:

IN -> Redshift_03.sql
DELETE FROM employees
USING departments d
WHERE employees.department = d.department_name
AND d.department_name = 'Sales';

SELECT * FROM employees ORDER BY id;

Output Code:

OUT -> Redshift_03.sql
DELETE FROM employees
USING departments d
WHERE employees.department = d.department_name
AND d.department_name = 'Sales';

SELECT * FROM employees ORDER BY id;

WITH clause

This clause specifies one or more Common Table Expressions (CTE). The output column names are optional for non-recursive CTEs, but mandatory for recursive ones.

Since this clause cannot be used in an DELETE statement, it is transformed into temporary tables with their corresponding queries. After the DELETE statement is executed, these temporary tables are dropped to clean up, release resources, and avoid name collisions when creating tables within the same session. Additionally, if a regular table with the same name exists, it will take precedence again, since the temporary table has priority over any other table with the same name in the same session.

Non-Recursive CTE

Input Code:

IN -> Redshift_04.sql
WITH sales_employees AS (
    SELECT id
    FROM employees
    WHERE department = 'Sales'
), engineering_employees AS (
    SELECT id
    FROM employees
    WHERE department = 'Engineering'
)
DELETE FROM employees
WHERE id IN (SELECT id FROM sales_employees)
   OR id IN (SELECT id FROM engineering_employees);

SELECT * FROM employees ORDER BY id;

Output Code:

OUT -> Redshift_04.sql
CREATE TEMPORARY TABLE sales_employees AS
SELECT id
FROM employees
WHERE department = 'Sales';

CREATE TEMPORARY TABLE engineering_employees AS
SELECT id
FROM employees
WHERE department = 'Engineering';

DELETE FROM
    employees
WHERE id IN (SELECT id FROM sales_employees)
   OR id IN (SELECT id FROM engineering_employees);
   
DROP TABLE sales_employees;
DROP TABLE engineering_employees;

SELECT * FROM
    employees
ORDER BY id;

Recursive CTE

Input Code:

IN -> Redshift_05.sql
WITH RECURSIVE subordinate_hierarchy(id, name, department, level) AS (
    SELECT id, name, department, 0 as level
    FROM employees
    WHERE department = 'Marketing'

    UNION ALL

    SELECT e.id, e.name, e.department, sh.level + 1
    FROM employees e
    INNER JOIN subordinate_hierarchy sh ON e.manager_id = sh.id
)
DELETE FROM employees
WHERE id IN (SELECT id FROM subordinate_hierarchy);

Output Code:

OUT -> Redshift_05.sql
CREATE TEMPORARY TABLE subordinate_hierarchy AS
   WITH RECURSIVE subordinate_hierarchy(id, name, department, level) AS (
       SELECT id, name, department, 0 as level
       FROM
           employees
       WHERE department = 'Marketing'

       UNION ALL

       SELECT e.id, e.name, e.department, sh.level + 1
       FROM
           employees e
       INNER JOIN
               subordinate_hierarchy sh ON e.manager_id = sh.id
   )
   SELECT
       id,
       name,
       department,
       level
   FROM
       subordinate_hierarchy;
       
   DELETE FROM
   employees
   WHERE id IN (SELECT id FROM
           subordinate_hierarchy
   );
   
   DROP TABLE subordinate_hierarchy;

Delete Materialized View

In Redshift, you can apply the DELETE statement to materialized views used for streaming ingestion. In Snowflake, these views are transformed into dynamic tables, and the DELETE statement cannot be used on dynamic tables. For this reason, an EWI will be added.

Input Code:

IN -> Redshift_06.sql
CREATE MATERIALIZED VIEW emp_mv AS
SELECT id, name, department FROM employees WHERE department = 'Engineering';

DELETE FROM emp_mv
WHERE id = 2;

Output Code:

OUT -> Redshift_06.sql
CREATE DYNAMIC TABLE emp_mv
--** SSC-FDM-0031 - DYNAMIC TABLE REQUIRED PARAMETERS SET BY DEFAULT **
TARGET_LAG='1 day'
WAREHOUSE=UPDATE_DUMMY_WAREHOUSE
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "02/11/2025",  "domain": "test" }}'
AS
SELECT id, name, department FROM
employees
WHERE department = 'Engineering';
        
        
!!!RESOLVE EWI!!! /*** SSC-EWI-RS0008 - MATERIALIZED VIEW IS TRANSFORMED INTO A DYNAMIC TABLE, AND THE DELETE STATEMENT CANNOT BE USED ON DYNAMIC TABLES. ***/!!!
DELETE FROM
emp_mv
WHERE id = 2;

Known Issues

  • Replicating the functionality of the WITH clause requires creating temporary tables mirroring each Common Table Expression (CTE). However, this approach fails if a temporary table with the same name already exists within the current session, causing an error.

Related EWIs

  1. SSC-FDM-0031: Dynamic Table required parameters set by default.

  2. SSC-EWI-RS0008: Delete statement cannot be used on dynamic tables.

PreviousCREATE DATABASENextINSERT

Last updated