Expunging data, the Snowflake way.

Sometimes, in Snowflake as well as in SQL Server, you’re forced to delete data. Hopefully, you’re not deleting from the main table of a database in Production, but that is definitely one way to wake up in the morning.

If you’re only deleting from one table, then Snowflake is like SQL Server in the formatting style.

DELETE FROM tableA WHERE a = 1;

If you need a refresher on how SQL Server does it, please refer back to a post I did on this not so long ago.

I can hear you now though. “Sherpa, what if I need to delete from 2 tables? Can I use a JOIN like I do in SQL Server?”

Sadly, my friend, Snowflake just doesn’t do that. You’ll need to use the USING clause to restrict your data as shown below.

This time, I have 3 rows in my Testing table and 1 row in my Testing1 table.

To DELETE data from Testing that is in Testing1, I would type in the following:

DELETE 
FROM Testing t0
    USING Testing1 t1
    WHERE t1.FirstName = t0.FIRSTNAME
        AND t1.LastName = t0.LASTNAME;

As you’ve probably noticed, it’s kinda like the JOIN statement, but not quite. Also notice that you can use an alias – and I highly recommend doing so.

Now, if you want to totally get rid of all of the data from a table, you can use our other deleting friend – TRUNCATE.

TRUNCATE TABLE Testing;

Now, if I have fully qualified the table or if I have previously set the database and schema to use for the session, I can do a little less typing:

TRUNCATE Testing;

But, and this one is a bit interesting, in an edge-case sort of way, if I don’t know for sure that the table exists, I can remove the data from it.

TRUNCATE IF EXISTS Testing;

And that, my friends, is how we delete data in Snowflake. A few things that are different from SQL Server, but not totally out there. Hopefully, you’ve learned a few things and can delete data as needed – just not too much or in the wrong environment. Until next time my friends!