In my scenario, the new data coming in are the current, valid records. Any records that are not in the new data should be labeled as 'Gone", any matching records should be labeled with "Updated". And finally, any new records should be added.
So in summary, in one merge statement I'd like to:
1. Add new rows from the current data
2. Update existing rows that are in the source table (status="Updated")
3. Update existing rows that are missing from the source table (status="Gone")
Is this possible? This is what I have so far - but it doesn't get me point 3.
Here is a non-working example of what I'm trying to do
CREATE or replace TABLE target (
id INT NOT NULL,
status VARCHAR(30) NOT NULL
);
INSERT INTO target (id, status)
VALUES (1, "Initial status"), (2, "Initial status"), (3, "Initial status");
CREATE or replace TABLE source (
id INT NOT NULL,
status VARCHAR(30) NOT NULL
);
INSERT INTO source (id, status)
VALUES (1, "Initial status"), (3, "Initial status"), (4, "Initial status") ;
MERGE INTO target t
USING source s
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET t.status = "Updated"
WHEN NOT MATCHED THEN INSERT (id, status) VALUES (s.id, s.status);
select * FROM target order by id;
I've tried putting conditions on 'WHEN NOT MATCHED' and then using 'UPDATE SET', but I get the error message:
Error in SQL statement: ParseException:
mismatched input 'SET' expecting 'INSERT'(line 6, pos 39)
Is this possible?
(I'm coming from SAS and this is super easy to do)
