Keep your existing PI (if it doesn't include the timestamp) and switch to a MERGE using WHEN NOT MATCHED only instead of INSERT/SELECT.
SyntaxEditor Code Snippet
Hi. I think I did as advised but I get the error 5758 (The search condition must fully specify the Target table primary index and partition column(s) and expression must match INSERT specification primary index and partition column(s).)
Can anyone spot the problem with these steps?
1. The master table which new data are merged into, was created like this - notice the UPI is everything except timestamp..........
CREATE SET TABLE MY_DB.my_table ( customernumber CHAR(16) NOT NULL , data_timestamp TIMESTAMP(6) , CustState CHAR(16) NOT NULL , Segment CHAR(14) NOT NULL)
UNIQUE PRIMARY INDEX ( customernumber, CustState, Segment) ;
2. Some initial data is inserted into the master table no problem.
3. The merge statement takes the same columns from OTHER_DB.similar_table and tries to update the Master table.
For each customer (identified by customernumber) we want to get the latest data, and if there is a new customer, add it.
The timestamp in OTHER_DB.similar_table is sure to be new, whereas for any given customernumber, the CustState and Segment values may have changed or not, we don't know.
MERGE MY_DB.my_table A USING (SELECT customernumber, data_timestamp, CustState, Segment FROM OTHER_DB.similar_table) AS B ON A.customernumber = B.customernumber WHEN MATCHED THEN UPDATE SET data_timestamp = B.data_timestamp, CustState = B.CustState, Segment = B.Segment WHEN NOT MATCHED THEN INSERT VALUES ( B.customernumber, B.data_timestamp, B.CustState, B.Segment); ...This throws the error. I tried explicitly stating the relevant logic in the 'ON' condition (below) but that didn't help. AND A.data_timestamp <> B.data_timestamp AND ( A. CustState <> B.CustState OR A.Segment <> B.Segment) Any advice much appreciated.
You must match on the full Primary Index (and Partitioning), which you clearly don't.
And your SQL doesn't match your initial description:
I would like a daily batch loading to add new rows to this table, but ONLY if one or more of the attributes changes (the timestamp always changes).
Based on this your MERGE should match on all columns but the timestamp and then there's no UPDATE but INSERT only.
And then you don't need that useless UPI (probably NUPI on customernumber)