CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platformsql-delete
Javier Lopez Tomas
Javier Lopez Tomas

Reputation: 2342

Delete from table using columns with null values Snowflake

I have an automatic process to make incremental inserts based on defined primary keys of the table. In a few tables, the primary key has null values and we cannot make anything to solve it at the root, so we have to deal with it.

Once the primary keys of the table are obtained, we run a query to delete records as follows:

DELETE FROM table
USING stg_table
WHERE table.pk_1 = stg_table.pk_1 AND table.pk_2 = stg_table.pk_2

This works except if some value in the pk_1 or pk_2 is null. I have done the following to solve it:

DELETE FROM table
USING stg_table
WHERE NVL(table.pk_1, 'null') = NVL(stg_table.pk_1, 'null')

However, this only works if the pk_1 is VARCHAR, otherwise, it will fail.

I could modify my automated script and get the type of each column and depending on that pass a different value to the NVL function, but I was wondering if there is any way of achieving correctly the deletion using just Snowflake.

Upvotes: 1

Views: 2473

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

The IS DISTINCT FROM is null-safe operator and it allow to compare tuples:

DELETE FROM table
USING stg_table
WHERE (table.pk_1,table.pk_2) IS NOT DISTINCT FROM (stg_table.pk_1, stg_table.pk_2);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

If you want NULL values to match, you can use:

DELETE FROM table
USING stg_table
WHERE (table.pk_1 = stg_table.pk_1 OR table.pk_1 IS NULL AND stg_table.pk_1 IS NULL) AND
      (table.pk_2 = stg_table.pk_2 OR table.pk_2 IS NULL AND stg_table.pk_2 IS NULL)

Or, use EQUAL_NULL(), Snowflake's NULL-safe comparison operator:

DELETE FROM table
USING stg_table
WHERE EQUAL_NULL(table.pk_1, stg_table.pk_1) AND
      EQUAL_NULL(table.pk_2, stg_table.pk_2);

All that said, primary keys are never NULL -- by the definition of SQL. And it is really rare to create databases where a NULL value in two different tables are supposed to match.

Upvotes: 2

Related Questions