20 Oct 18 · Asran ·       Add to Favorites   Report

DELETE FROM USING command in Snowflake

Delete command is used to remove data from a table using an optional WHERE clause and/or additional tables

Example:

Delete from Employee;

Syntax:

DELETE FROM <table_name>
[ USING <additional_tables> ]
[ WHERE <condition_query> ]

For example, given tables tab1 and tab2 with columns (k number, v number):

select * from tab1;
-------+-------+
k | v |
-------+-------+
0 | 10 |
-------+-------+
select * from tab2;
-------+-------+
k | v |
-------+-------+
0 | 20 |
0 | 30 |
-------+-------+

If you run the following query, both rows of tab1 are joined against the single row of tab2:

 delete from tab1 using tab2 where tab1.k = tab2.k

Because at least one joined pair satisfies the condition, the row is deleted. As a result, after the statement completes, tab1 is empty

Asran

posted on 20 Oct 18

Read great educational content like this and a lot more !

Members get free exclusive access to content, new courses, and discounts. Signup for a free account to write a post / comment / upvote posts. Creating an account takes less than 5 seconds and you can start earning badges & points too

Copied