I created a MultiSet-Valid time Temporal table with 5 columns(validtime column + 4 cols) with col1 and col2 being a primary index. I did populate this table with data from another table.
What I am trying to achieve here is Type II SCD, On key match see if non-key columns changed and if yes expire current row and insert a new row (here we are using validtime column to handle eff,expiry dates)
And since temporal column update is not valid on SEQUENCED VALIDTIME, I used following query
from table 2
set validtime_column = case when table1.col3 <> table2.col3 or
table1.col4 <> table2.col4
then period(begin(table1.validtime_column), cast(begin(table2.validtime_column) as date) -1)
where table1.col1 = table2.col1
and table1.col2 = table2.col2
and end(table1.validtime) is UNTIL_CHANGED -- just to pick current rows from the table ???
But the problem I am running into is, query is failing saying "taregt rows updated my multiple source rows". But I am pretty sure that for a row in target table there is only one row in source.
My concern here is, has it got anything do to with column being an Temporal column ?? And if Yes, can this be handled in any other way ??