Replace NULL values with values from join results

Question

I have a table where the value of a column can be NULL because those records have been created in the past. But now I would like to update those records. The tables are as follows:

TABLE 1
id | workflowId | workflowName | workflowVersion
1  | NULL       | 'name-1'     | 2
2  | NULL       | 'name-1'     | 3
3  | NULL       | 'name-1'     | 3
4  | NULL       | 'name-1'     | 3
5  | 2          | 'name-2'     | 1

TABLE2 
workflowId | workflowName | workflowVersion | target
1          | 'name-1'     | 2               | 'master'
2          | 'name-1'     | 3               | 'master'
3          | 'name-1'     | 4               | 'something'
4          | 'name-2'     | 1               | 'master'

It's a MySQL database.
In TABLE1 I would like to update all the NULL values for workflowId with the actual workflow with the result of the query:

SELECT workflowId 
FROM table2 as t2, table1 as t1  
WHERE t2.workflowName = t1.workflowName 
and t2.workflowVersion = t1.workflowVersion 
and t2.target = 'master'

Another thing to notice is that TABLE1 is much bigger than TABLE2, and executing that join for each record in TABLE2 has a big cost. Ideally, I would like also to reduce the computational costs in parsing all the records in TABLE1, see if they have correspondences in TABLE2 and to update them (as opposite of executing that join for each record in TABLE2).
However, as long as I can find something that works for my case I would be happy.

FYI: learn.microsoft.com/en-us/previous-versions/visualstudio/… — Gen Wan
– Gen Wan, Commented Dec 9, 2019 at 18:01

Gordon Linoff · Accepted Answer · 2019-12-09 17:59:53Z

1

You seem to want an update with join:

update table1 t1 join
       table2 t2
       on t2.workflowName = t1.workflowName and
          t2.workflowVersion = t1.workflowVersion 
    set t1.workflowId = t2.workflowId
    where t2.target = 'master' and t1.workflowId is null;

answered Dec 9, 2019 at 17:59

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

zip · Accepted Answer · 2019-12-09 17:59:08Z

0

Try this:

with cte as(
Select * from TABLE1 where workflowId is null
)
update cte 
set workflowId = table2.workflowId 
from cte inner join TABLE2 on cte.workflowName = table2.workflowName

answered Dec 9, 2019 at 17:59

zip

4,0612 gold badges13 silver badges22 bronze badges

Collectives™ on Stack Overflow

Replace NULL values with values from join results

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related