Snowflake merge using same table

Question

I have a table whose Primary key is 1 and the other column is roll number.

I want to check if the id value is present in table, if yes update its roll number. If not insert the id and roll number in table.

I am trying to do it using merge.

parameters: id_value, roll_number


merge into "test_table"  as t
using (SELECT * from "test_table" where id = id_value) as s
on t.id=s.id
when matched then
    update set t.roll_number=5
when not matched then
    insert (id, roll_number) values (id_value,roll_number);

It works when the value exists it updates the table but not in case of insert. No row is inserted.

score 1 · Accepted Answer · answered Mar 30 '21 at 17:48

You should be able to do this with a merge statement that looks like this:

-- Set params first
set (id_value, roll_number) = (1, 5);

-- Run merge
merge into test_table
    using (select
               column1 as id,
               column2 as roll_number
           from
           values ($id_value, $roll_number)) tt
    on test_table.id = tt.id
    when matched then update set test_table.roll_number = tt.roll_number
    when not matched then insert (id, roll_number) values (tt.id, tt.roll_number);

Full example:

-- Set up example table and insert values
create temporary table test_table
(
    id          number,
    roll_number number
);

-- Insert some sample data
insert overwrite into test_table
values (1, 2),
       (2, 1),
       (3, 6);

-- Here is what the initial table looks like
select *
from test_table;
-- +--+-----------+
-- |ID|ROLL_NUMBER|
-- +--+-----------+
-- |1 |2          |
-- |2 |1          |
-- |3 |6          |
-- +--+-----------+

-- Set the parameters
set (id_value, roll_number) = (1, 5);

-- Run the merge statement using the parameters.
-- This should update the roll_number with ID of 1 to value 5.
merge into test_table
    using (select
               column1 as id,
               column2 as roll_number
           from
           values ($id_value, $roll_number)) tt
    on test_table.id = tt.id
    when matched then update set test_table.roll_number = tt.roll_number
    when not matched then insert (id, roll_number) values (tt.id, tt.roll_number);

-- Check what the table looks like now
select *
from test_table;
-- +--+-----------+
-- |ID|ROLL_NUMBER|
-- +--+-----------+
-- |1 |5          | <---- Updated row
-- |2 |1          |
-- |3 |6          |
-- +--+-----------+

-- Set the parameters to an id that doesn't exist in the table.
set (id_value, roll_number) = (4, 3);

-- Now the same merge statement should insert
-- a new record with ID 4 and roll_number 3.
merge into test_table
    using (select
               column1 as id,
               column2 as roll_number
           from
           values ($id_value, $roll_number)) tt
    on test_table.id = tt.id
    when matched then update set test_table.roll_number = tt.roll_number
    when not matched then insert (id, roll_number) values (tt.id, tt.roll_number);

-- Check what the table looks like now
select *
from test_table;
-- +--+-----------+
-- |ID|ROLL_NUMBER|
-- +--+-----------+
-- |4 |3          | <---- New row
-- |1 |5          |
-- |2 |1          |
-- |3 |6          |
-- +--+-----------+

score 0 · Answer 2 · answered Mar 30 '21 at 12:59

0

When source and target are the same table, it's not going to be in the source if it's not existing in the target. So there's nothing to insert.

answered Mar 30 '21 at 12:59

Cedersved

1,015
1
7
21

1

So what is the best way to select and check if data exists if yes run update command or if not run insert command? Is there any better or efficient way of doing the same? – Avenger Mar 30 '21 at 14:02

Snowflake merge using same table

2 Answers2