CODEWITHSUNDEEP
mysqlsqlrelational-database
cloudy_weather
cloudy_weather

Reputation: 2977

Replace NULL values with values from join results

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.

Upvotes: 0

Views: 47

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

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;

Upvotes: 1

zip
zip

Reputation: 4061

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

Upvotes: 0

Related Questions