1
Good evening, I need to update a table 'a' in the rows where there is no occurrence in table 'b', where table b has about 7 million records. I imagined two solutions but I can not say which would be the most performative solution. Follow :
Solution 1 :
update a
left join b on a.coluna = b.id
set a.coluna = null
where a.coluna is not null
and b.id is null ;
Solution 2 :
update a
set a.coluna = null
where a.coluna not in (select id from b);
Any ideas/suggestions?
You can also use the
NOT EXISTS:UPDATE a SET a.coluna = NULL WHERE NOT EXISTS (SELECT 1 FROM b WHERE a.coluna = b.id);. If there is an index on the b.id column I think the performance will be better.– anonimo
@anonimo thanks. In terms of performance the solution with left Join was the best but in some executions generated a deadlock. In the end, I ended up opting for the alternative with not exists'
– vieiragui