20 October 2018        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


Read great educational content like this and a lot more ! Create my free account now 🎁

Copied