SQL Server 2014 Replace Distinct? DeIdentify Data

0
votes

I am going to explain again what I am trying to do in hopes that you can help.

Table 1 has 4061 rows with columns that include [Name],[Address1],[Address2],[Address3],[City],[State],[Zip],[Country],[Phone] and 20 other columns. Table 1 is data that needs to be deidentified. Table 1 has 1534 distinct [Name] rows out of 4061 rows total.

Table 2 has auto generated data which includes the same columns. I would like to replace the above mentioned columns in table 1 with data from table 2. I want to select distinct based on [Name] from table one and then [Name],[Address1],[Address2],[Address3],[City],[State],[Zip],[Country],[Phone] with a new set of distinct data from table 2.

I do not want to just update each row with a new address as that will screw up the data consistency. By replacing only distinct this will allow me to preserve the data consistency while changing the row data in table 1. When I am done I would like to have 1534 distinct new de-identified [Name] [Address1],[Address2],[Address3],[City],[State],[Zip],[Country],[Phone] in table 1 from table 2.

2
sqlsql-serversql-server-2014
With an update statement? - Sean Lange
But wouldn't I have to specify an update statement for each one where this = this sort of thing? I don't want to write 1500 update statements. I feel like there is something big I am not understanding. The autogen data is completely diff data so it does not match but I want it to replace the old data consistently. There is more than 1500 rows but only 1500 distinct address's. - David
You have me at a serious disadvantage here. I can't see your screen and don't have any knowledge of what you are trying to do. If you have an identity column in both tables you could use that. If not, you could leverage ROW_NUMBER in either or both to use as a join criteria. - Sean Lange
I updated my explanation above, does that help? - David
You keep using the word distinct but in context of your question it doesn't make much sense. Did you try to approach that Gordon posted. That is the same technique I described above. - Sean Lange

2 Answers

1
votes

You would use join in the update. You can generate a join key for 1500 rows using row_number():

update toupdate
    set t.address = f.address
    from (select t.*, row_number() over (order by newid()) as seqnum
          from table t
         ) toupdate join
         (select f.*, row_number() over (order by newid()) as seqnum
          fake f
         ) f
         on toupdate.seqnum = f.seqnum and t.seqnum <= 1500;
0
votes

Here is how I ended up doing it. First I ran a statement to select distinct and inserted it into a table.

Select Distinct [Name],[Address1],[City],[State],[Zip],[Country],[Phone]
INTO APMAST2
FROM APMAST

I then added name2 column in APMAST2 and used a statement to create a sequential id field into APMAST2.

DECLARE @id INT 
SET @id = 0 
UPDATE APMAST2
SET @id = id = @id + 1 
GO

Now I have my distinct info plus a blank name field and a sequential ID field in APMAST2. Now I can join this date with my fakenames table which I generated from. HERE using their bulk tool.

Using a Join Statement I joined my fake data with APMAST2

Update dbo.APMAST2
    SET dbo.APMAST2.Name = dbo.fakenames.company,
        dbo.APMAST2.Address1 = dbo.fakenames.streetaddress,
        dbo.APMAST2.City = dbo.fakenames.City,
        dbo.APMAST2.State = dbo.fakenames.State,
        dbo.APMAST2.Zip = dbo.fakenames.zipcode,
        dbo.APMAST2.Country = dbo.fakenames.countryfull,
        dbo.APMAST2.Phone = dbo.fakenames.telephonenumber       
        FROM 
        dbo.APMAST2
        INNER JOIN
        dbo.fakenames
        ON dbo.fakenames.number = dbo.APMAST2.id

Now I have my fake data loaded but I kept my original Name field so I could reload this data into my full table ARMAST so now I can do a join between ARMAST2 and ARMAST.

Update dbo.APMAST
    SET dbo.APMAST.Name = dbo.APMAST2.Name,
        dbo.APMAST.Address1 = dbo.APMAST2.Address1,
        dbo.APMAST.City = dbo.APMAST2.City,
        dbo.APMAST.State = dbo.APMAST2.State,
        dbo.APMAST.Zip = dbo.APMAST2.Zip,
        dbo.APMAST.Country = dbo.APMAST2.Country,
        dbo.APMAST.Phone = dbo.APMAST2.Phone        
        FROM 
        dbo.APMAST
        INNER JOIN
        dbo.apmast2
        ON dbo.apmast.name = dbo.APMAST2.name2

Now my original table has all fake data in it but it keeps the integrity it had , well most of it, so the data looks good when reported on but is de-identified. You can now remove APMAST2 or keep it if you need to match this with other data later on. I know this is long and I am sure there is a better way to do it but this is how I did it, suggestions welcome.