OK, it's guaranteed that if b.issn1=b.issn2 or b.issn=b.eissn or b.title_sci=b.title then b.cc=0, otherwise b.cc=1.
> if b.cc<>0, consult the master and update the b.cc based on m.cc for this row.
That appears to mean: if b.cc <> 0 then set b.cc=m.cc for that row.
So now the spec looks simpler ...
In a join of b to m on (title_sci, title, issn1,issn2, issn, eissn, databas) ...
if b.title_sci=b.title or b.issn1=b.issn2 or b.issn=b.eissn then
already b.cc=0, do nothing
end if
if b.cc <> 0 then
update b set b.cc=m.cc
where b.title_sci=m.title_sci and b.title=m.title and b.issn1=m.issn1 and b.issn=m.issn and b.eissn=m.eisssn;
end if
Does it look correct to you?
An assumption of your requirement is that there's exactly one matching m row for each combination of title, title_sci, issn1, issn, eissn, databas values in b. Problems with the datasets you posted prevent me from checking that assumption.
So try this query in your system ...
select b.title,b.title_sci,b.issn1,b.issn2,b.issn,b.eissn,b.databas,count(*) as N
from base_report b
join master_data m using (title,title_sci,issn1,issn2,issn,eissn,databas)
group by b.title, b.title_sci, b.issn1, b.issn2, b.issn, b.eissn, b.databas
having N<>1;
If it produces any rows, the above assumption is false.
Edited 1 time(s). Last edit at 09/26/2017 01:35PM by Peter Brawley.