Snowflake merge using same table

user_12965658 0
Viewed 518

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.

snowflake-schema snowflake-cloud-data-platform
2 Answers

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          |
-- +--+-----------+
user_32872500

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.

user_40654280
Related