DELETE
Last updated
Last updated
Deletes rows from tables. ().
This syntax is fully supported in Snowflake.
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.
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.
This clause introduces a list of tables when additional tables are referenced in the WHERE clause condition. This clause is fully equivalent in Snowflake.
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.
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.
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 over any other table with the same name in the same session.
In Redshift, you can apply the DELETE statement to materialized views used for . 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.
: Dynamic Table required parameters set by default.
: Delete statement cannot be used on dynamic tables.