MySQL Forums
Forum List  »  Stored Procedures

Re: Display all the records from table using cursor
Posted by: Peter Brawley
Date: September 26, 2017 01:34PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Display all the records from table using cursor
606
September 26, 2017 01:34PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.