How to update table from another table, with 2 columns

I am using SQL Server database and want a way to update MachinesSummary.ShareCount.

Here are my two tables

MachinesSummary

ID Machine1 Machine2 ShareCount
-------------------------------
1  A        J        NULL
2  K        S        NULL
3  A        E        NULL
4  J        A        NULL
5  Y        U        NULL
6  S        W        NULL
7  G        A        NULL
8  W        S        NULL

The other table is MachineDetails

ProcessNo Machine
------------------
1         A
1         H
1         W
2         A
2         J
2         W
3         Y
3         K
4         J
4         A

I want to update ShareCount in the MachineSummary table with the count of processes that both Machine1 and Machine2 share.

For record 1 in the MachineSummary table, I want the number of processes both share in MachineDetails which is 1 in this case

While for record 4 the ShareCount is 2

I tried this

UPDATE M
SET ShareCount = COUNT(DISTINCT X.ProcessNo)
FROM 
    (SELECT ProcessNo, ',' + STRING_AGG(Machine,',') + ',' Machines
     FROM MachineDetails
     GROUP BY ProcessNo) X 
INNER JOIN MachinesSummary M ON X.Machines LIKE '%'+ M.Machine1 + '%' 
                             AND X.Machines LIKE '%'+ M.Machine2 + '%'

But I wonder if there is an easier high performance way

The MachineDetails table has 250 million rows.

Answer

Well, I would use a self-join to get the number of combinations:

UPDATE M
    SET ShareCount = num_processes
FROM MachinesSummary M JOIN
     (SELECT md1.Machine as machine1, md2.Machine as machine2, COUNT(*) as num_processes
      FROM MachineDetails md1 JOIN
           MachinesDetails md2
           ON md1.processno = md2.processno
      GROUP BY md1.Machine, md2.Machine
     ) md
     ON md.Machine1 = M.machine1 AND md.Machine2 = M.machine2;

Advertisement

Answer