Inserting values into a table and delete rows based on a condition using merge in snowflake

user_13138455 0
Viewed 1575

I have a table with DBName, SCName, Number. I wrote a procedure that inserts the DBName, SCName into the table and deletes a row when a Number is 0. I used merge to avoid duplicates and insert based on the condition but I don't understand how to delete a row when a Number=0.

------------------------
|DBName| SCName| Number|
|  DB1 |  SC1  |   1   |
|  DB2 |  SC2  |   0   | <-- Need to delete row
|  DB2 |  SC3  |   2   |
|  DB2 |  SC1  |   4   |
|  DB3 |  SC4  |   0   | <-- Need to delete row
------------------------

Here is my Procedure:

CREATE TABLE TABL(DBName VARCHAR, SCName VARCHAR); // creating table

CREATE OR REPLACE PROCEDURE repo(DB VARCHAR,SC VARCHAR) 
    RETURNS string
    LANGUAGE JAVASCRIPT
    AS
    $$      
        //inserting values into table using merge
        //if values are already present and Number = 0 then I need to delete row
        var sql_command = `merge into TABL as t 
                            using (SELECT :1 as database,:2  as schema) as s 
                            on t.DBName = s.database 
                            and t.SCName = s.schema 
                            when matched then update 
                            set t.DBName = t.DBName 
                            when not matched then insert 
                            (DBName, SCName) VALUES (:1,:2)`;
        snowflake.execute({sqlText: sql_command, binds: [DB, SC]});

    return 'success';
    $$;
snowflake-cloud-data-platform stored-procedures sql javascript
1 Answers

I didn't understand in which case (MATCHED / NOT MATCHED) the rows with '0' should be deleted from the target table.

But in general you have only can use DELETE in the MATCH-case:

when matched and number=0 then delete

It is important to avoid a nondeterministic result for the merge (see link below under "Duplicate Join Behavior"). Solution therefor is to also add "and number!=0" to your "when matched then update"-clause.

More infos: https://docs.snowflake.com/en/sql-reference/sql/merge.html

EDIT: I posted wrong information. For MATCH you can Delete and Update, for NOT MATCH you can INSERT.

user_124804310
Related