Update table value from a second table by joining a third table

Question

I have 3 tables, TableA, TableB and TableC

I want to migrate the data from TableA column DateA to TableB column DateB, only if the date field is null in table B (I don’t want to overwrite any existing data)

I need to join TableA and TableB using TableC to match the A and B rows that should be updated by their RobotNumber.

TableA

RobotNumber         DateA
11                  12/12/2015
12                  01/05/2018
13                  05/03/2019

TableB

RobotID       Date
2             null
3             07/01/2018
4             null

TableC

RobotNumber RobotID      
11          2
12          3
13          4

I have tried to join the tables and do the update like so but the query times out:

UPDATE TableB 
SET TableB.DateB = TableA.DateA
FROM TableB 
inner join TableC c on TableB.RobotID = c.RobotID
inner join TableA a on a.RobotNumber = c.RobotNumber
where TableB.RobotID not in (select RobotID from TableB where DateB is not null)

Gordon Linoff Gordon Linoff · Accepted Answer · 2020-06-17T11:50:10

I would start by getting rid of the NOT IN:

UPDATE TableB 
    SET TableB.DateB = TableA.DateA
    FROM TableB JOIN
         TableC c 
         ON TableB.RobotID = c.RobotID JOIN
         TableA a 
         ON a.RobotNumber = c.RobotNumber
    WHERE TableB.DateB is null;

Then, you should also have indexes on TableC(RobotID) and TableA(RobotNumber, DateA).

Update table value from a second table by joining a third table

2 Answers