SQL Merging Associated Records

Let's say we have a database with a table that has many other associated tables. If you diagrammed the database, this would be the table at the center with many foreign key relationships spiraling out of it.

To make it more concrete, let's say the two records in this central table are Initech and 开发者_运维百科Contoso. Initech and Contoso are both associated with many other records in associated tables like Employees, AccountingTransactions, etc. Let's say the two merged (Initech bought Contoso) and from a data standpoint, it really is as simple as merging all the records. What's the easiest way to take all of Contoso's related records, make them point to Initech and then delete Contoso?

UPDATE with CASCADE comes tantalizingly close, but it obviously can't work without turning off constraints and then turning them back on (yuck).

Is there a nice generic way to do this without hunting down every single linked table and migrating them one by one? This has to be a common requirement. It's come up in two places in this project and can be summed up with: Entity A needs to control everything Entity B current controls. How can I make it happen?

Before Merge:

Companies
ID Name
1  Contoso
2  Initech

Employees
ID Name CompanyId
1  Bob  1 
2  Ted  2

After Merge:

Companies
ID Name
2  Initech

Employees
ID Name CompanyId
1  Bob  2 
2  Ted  2

All my attempts at searching only turned up questions about merging separate databases... so sorry if this has been asked before.

This query is likely vendor-dependent, but in MySQL:

UPDATE Employees e, Cars c, OtherEntity o
  SET e.CompanyId = 2, c.CompanyId = 2, o.CompanyID = 2
  WHERE e.CompanyID = 1 OR c.CompanyId = 1 OR o.CompanyId = 1;

Succinctly, no; there isn't a generic way to do it.

Consider your sample database with tables Companies, Employees, Departments, and AccountingTransactions.

You need to delete one of the company records (because after the merger, you will only record the current state of affairs).

You need to alter the employee records to change the employing company. However, it is quite possible that there is an employee number N in both companies, and one of those (presumably Contoso's) will have to be assigned a new employee number.

You probably face the problem that department 1 in Conotoso's data is Engineering, but in Initech's is Finance. So, you need to worry about how you are going to map the department numbers between the two companies, and then you face the problem of assigning Contoso's employees to Initech's departments.

For the historical accounting transactions, you probably have to keep Contoso's historical accounting records in Contoso's name, while some (of the most recent) transactions will need to be migrated to Initech's name. So maybe you won't be deleting the Contoso record from the table of companies after all, but you won't be able to use it to identify any new records.

These are just a small sampling of the reasons why such mappings cannot readily be automated.

No, there's no simple generic way of merging rows and cascading those changes throughout your system. You can script it all - which may be the best way, depending on your scenario - or devise a workaround.

One workaround might be to implement a parenting pattern on your central table (or abstract it to another table). You would then end up with something like

Companies 
ID ParentID Name 
1  2        Contoso 
2  null     Initech  

or

Companies 
ID ParentID Name 
1  3        Contoso 
2  3        Initech  
3  null     MegaInitech

and all your queries that join onto this central Companies table now check ID and ParentID;

SELECT * 
FROM Employees          
WHERE CompanyId IN (SELECT ID FROM Companies WHERE ID = @id OR ParentID = @ID)

Abstract this away to a view or function

CREATE FUNCTION fn_IsMemberOf
(
    @companyId INT,
    @parentId INT
)
RETURNS BIT
AS
BEGIN   
    DECLARE @result BIT = 0
    SELECT @result = 1 FROM Companies 
    WHERE ID = @companyId
        AND COALESCE(ParentID, ID) = @parentID
    RETURN @result
END

SELECT * 
FROM Employees          
WHERE fn_IsMemberOf(CompanyId, 1) = 1

(haven't tested this but you get the idea)