I had created one source table,it has two columns (ID,NAME) ID is the primary key. And I have created dynamic table (names) which works for insert and delete operation but update operation is not working. I need help to solve this issue. Can someone provide solution for how to update the rows in dynamic table?
source table
| ID | Name |
|---|---|
| 1 | nick |
| 1 | nick |
| 2 | john |
| 2 | john |
| 2 | michel |
Dynamic Table
CREATE OR REPLACE DYNAMIC TABLE names
TARGET_LAG = '1 minute'
WAREHOUSE = "COM_WH"
SELECT ID,NAME,current_timestamp() FROM SOURCE_TABLE;
names
| ID | Name |
|---|---|
| 1 | nick |
| 1 | nick |
| 2 | john |
| 2 | john |
| 2 | michel |
Expecting Result from dynamic table:
| ID | Name |
|---|---|
| 1 | nick |
| 2 | michel |
But i'm getting duplicate records in dynamic table.
https://docs.snowflake.com/en/user-guide/dynamic-tables-about
In the above link they gave example for task and stream replace with dynamic table
In task i can do update if the id's are same but dynamic table i cant do update