SQL update - merge data

Question

Issue with a USER_DIM table in a database that has references to other tables in that database like USER_ACTIVITY_FACT via USER_ID.

A problem arouse when the original design of the table did not use a proper unique identifier which actually comes from a different database's EXTERNAL_ID.

I can figure how to select the extra rows or delete them but I want to update the rows in the other tables to point to the original USER_ID and then delete the extra rows in the USER_DIM table

SELECT ACTIVITY_FACT.USER_ID FROM USER_ACTIVITY_FACT WHERE USER_ACTIVITY_FACT.USER_ID IN ( select USER_ID FROM USER_DIM WHERE EXTERNAL_ID IN (SELECT ud2.EXTERNAL_ID FROM USER_DIM as ud2 where USER_ID > ud2.USER_ID));

update these to be the smallest USER_ID

And then execute the delete statement on the USER_DIM;

DELETE FROM USER_DIM WHERE EXTERNAL_ID IN (SELECT ud2.EXTERNAL_ID FROM USER_DIM as ud2 where USER_ID > ud2.USER_ID);

after which ALTER the table to have Unique index on the EXTERNAL_ID column.

This query might update per row rather than a more preferred update multiple rows at once for the offending-extra USER_ID.

Thanks in advance for your help!

UPDATE To clarify the objective:

USER_ACTIVITY_FACT ------------- USER_ID 2 3 4 5 6 USER_DIM -------------- USER_ID EXTERNAL_ID 2 23 3 23 4 24 5 24 6 26

..result should look like

USER_ACTIVITY_FACT ------------- USER_ID 2 2 4 4 6 USER_DIM -------------- USER_ID EXTERNAL_ID 2 23 4 24 6 26

hope this helps

Nenad Zivkovic · Accepted Answer · 2013-04-12 21:13:27Z

Not sure if I got the request correctly but here is something I came up with. You can use group by to find your minimum USER_ID for each EXTERNAL_ID and put that in temp table as your mapping info (OLD_ID => NEW_ID). Afterwards you join your tables that need updating on temp table and update from old to new id (Join on OLD_ID, Update to NEW_ID). At the end you can delete like you used to, or again join to mapping table.

You can check on SQLFiddleDemo

--prepare data and insert into #mapping temp table from dim WITH CTE1 AS ( SELECT EXTERNAL_ID, MIN(USER_ID) AS NEW_USER_ID FROM dbo.USER_DIM GROUP BY EXTERNAL_ID ) SELECT CTE1.EXTERNAL_ID , USER_ID AS OLD_USER_ID , NEW_USER_ID INTO #mapping FROM dbo.USER_DIM INNER JOIN CTE1 ON dbo.USER_DIM.EXTERNAL_ID = CTE1.EXTERNAL_ID; --check your mappings SELECT * FROM #mapping; --update fact table based on join on mappings UPDATE fact SET fact.USER_ID = src.NEW_USER_ID FROM #mapping src INNER JOIN dbo.USER_ACTIVITY_FACT fact ON src.OLD_USER_ID = fact.USER_ID; --check your fact table SELECT * FROM USER_ACTIVITY_FACT; --delete from dim based on mappings missmatch DELETE d FROM dbo.USER_DIM d INNER JOIN #mapping m ON d.USER_ID = m.OLD_USER_ID WHERE m.NEW_USER_ID <> m.OLD_USER_ID; --check your dim table SELECT * FROM dbo.USER_DIM;

Oleksandr Fedorenko · Accepted Answer · 2013-04-12 21:37:54Z

Use UPDATE from a derived table with OUTPUT clause

DECLARE @delUserID TABLE(delUserID int) UPDATE x SET x.USER_ID = x.NewUserID OUTPUT DELETED.USER_ID INTO @delUserID FROM ( SELECT f.USER_ID, MIN(f.USER_ID) OVER(PARTITION BY u.EXTERNAL_ID) AS NewUserID FROM dbo.USER_ATIVITY_FACT f JOIN dbo.USER_DIM u ON f.USER_ID = u.USER_ID ) x WHERE x.USER_ID != x.NewUserID DELETE USER_DIM WHERE USER_ID IN (SELECT delUserID FROM @delUserID)

Demo on SQLFiddle

iowatiger08 · Accepted Answer · 2013-04-15 13:44:13Z

Here is what I came up with. @Nenad solutions works but I have to push this through liquibase scripts and was not sure if the SQL Server functions would work. I checked this against SQLFiddle as well.

--prepare data and insert into #mapping temp table from dim UPDATE USER_ACTIVITY_FACT SET USER_ID = (SELECT MIN(ud1.USER_ID) FROM USER_DIM as ud1 WHERE ud1.EXTERNAL_ID = (SELECT MIN(ud2.EXTERNAL_ID) FROM USER_DIM as ud2, USER_DIM as ud3 WHERE ud2.EXTERNAL_ID = ud3.EXTERNAL_ID AND ud3.USER_ID = USER_ACTIVITY_FACT.USER_ID) ); --delete from dim based on mappings missmatch DELETE FROM USER_DIM WHERE EXISTS (SELECT * FROM USER_DIM t1 WHERE t1.EXTERNAL_ID = USER_DIM.EXTERNAL_ID AND USER_DIM.USER_ID > t1.USER_ID);

Collectives™ on Stack Overflow

SQL update - merge data

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related