title: Inserting values into a table and delete rows based on a condition using merge in snowflake
body: <p>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.</p>
  <pre><code>------------------------
  |DBName| SCName| Number|
  |  DB1 |  SC1  |   1   |
  |  DB2 |  SC2  |   0   | &lt;-- Need to delete row
  |  DB2 |  SC3  |   2   |
  |  DB2 |  SC1  |   4   |
  |  DB3 |  SC4  |   0   | &lt;-- Need to delete row
  ------------------------
  </code></pre>
  <p>Here is my Procedure:</p>
  <pre><code>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';
      $$;
  </code></pre>