Random row selection in table to table synchronization.

devi_sree

I have a requirement where the data in one table need to be updated with corresponding data in another table. There is a join column as well which I can use. But the issue is, I need the update to be random. 
For eg:
In table1 there are 3 columns: 
name1,acctnum1,acctType1
Another table2:
acctType2, acctnum2

Here the acctType column will be join column and there can be multiple aactnum2 for one acctType.
So when I update acctnum1 column in table1 with acctnum2 using where accType1=acctType2, it should be random.
Now what happens is that all acctnum1 having same accType1 is replaced with same value , although multiple values of acctType2 are available in table2 for same acctType.

Is there a way to achieve this?

TheMaskedData

@devi_sree I think you'd be better off forgetting the join and just using a synchronisation manager for this.

Create a substitution rule on acctnum1 for however you want it masked, then in the change managers tab convert it to a sync manager. Then add the acctnum2 column as a target for the manager.

It will create a distinct list of the acctnums in both columns, map it to a new masked randomized value and then mask this back in maintaining the referential integrity.

There's a good example on RG University here: Masking Primary and Foreign Keys - Advanced Operations with Data Masker for SQL Server - Redgate University (red-gate.com)

Just ignore that the video is targeted towards PK/FK relationships - it will still have the outcome you want.

Let me know how you get on!

devi_sree

Thank you @TheMaskedData for the answer.

In my use case I want acctnum1 to have the same values in acctnum2 column(bcos there is a decoding algorithm that  will convert the masked value to valid data, so random synchronization will not work). So while preserving the acctnum2 values I need these values to be randomly updated in acctnum1 column.

TheMaskedData

@devi_sree I'm not sure I fully understand - are you able to provide an example with some example data perhaps? Like an ideal before and after so I can think about it a bit more and suggest a better example.

devi_sree

table2:
accttype    acctnum
1                  xxxxx123xxxx
1                  xxxx452xxxx
1                  xxxx654xxxx
2                 xxxxx487xxxx

table1:
name    accttype        acctnum
Joe       1                     123456
John     1                     456248
Jame    2                     154785
Jxxx     1                      457885

Now I want to synchronize table1 with table2 based on accttype column. Now when I do table to table sync, the output is:

table1:
name    accttype        acctnum
Joe       1                     xxxxx123xxxx
John     1                     xxxxx123xxxx
Jame    2                     xxxxx487xxxx
Jxxx     1                      xxxxx123xxxx

The expected output is:
name    accttype        acctnum
Joe       1                     xxxxx123xxxx
John     1                     xxxx452xxxx
Jame    2                     xxxxx487xxxx
Jxxx     1                       xxxx654xxxx

So, if you see the output I get, all acctnum for accttype 1 are same. But I need that to be randomly updated as shown.