MySQL Forums
Forum List  »  Stored Procedures

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

> Yep this logic works

All right, then on that logic, the following query ...

select b.base_report_id as ID, b.cc, m.cc, b.title, b.title_sci, b.issn1, b.issn2, b.issn, b.eissn 
from base_report_gt_zero b
join master_data m using (title_sci, title, issn1,issn2, issn, eissn, databas);
+-----+----+----+----------------------------------------------+----------------------------------------------+-----------+-----------+-----------+-----------+
| ID  | cc | cc | title                                        | title_sci                                    | issn1     | issn2     | issn      | eissn     |
+-----+----+----+----------------------------------------------+----------------------------------------------+-----------+-----------+-----------+-----------+
|  30 |  3 |  3 | Acta Orthopaedica (Supplement)               | Acta Orthopaedica                            | 1745-3674 | 1745-3682 | 1745-3690 | 1745-3704 |
|  31 |  3 |  3 | Acta Orthopaedica (Supplement)               | Acta Orthopaedica                            | 1745-3674 | 1745-3682 | 1745-3690 | 1745-3704 |
|  32 |  3 |  3 | Acta Orthopaedica (Supplement)               | Acta Orthopaedica                            | 1745-3674 | 1745-3682 | 1745-3690 | 1745-3704 |
|  33 |  3 |  3 | Acta Orthopaedica (Supplement)               | Acta Orthopaedica                            | 1745-3674 | 1745-3682 | 1745-3690 | 1745-3704 |
|  34 |  3 |  3 | Acta Orthopaedica (Supplement)               | Acta Orthopaedica                            | 1745-3674 | 1745-3682 | 1745-3690 | 1745-3704 |
|  35 |  3 |  3 | Acta Orthopaedica (Supplement)               | Acta Orthopaedica                            | 1745-3674 | 1745-3682 | 1745-3690 | 1745-3704 |
|  36 |  3 |  3 | Acta Orthopaedica (Supplement)               | Acta Orthopaedica                            | 1745-3674 | 1745-3682 | 1745-3690 | 1745-3704 |
|  37 |  3 |  3 | Acta orthopaedica                            | Acta orthopaedica. Supplementum              | 1745-3690 | 1745-3704 | 1745-3674 | 1745-3682 |
|  38 |  3 |  3 | Acta orthopaedica                            | Acta orthopaedica. Supplementum              | 1745-3690 | 1745-3704 | 1745-3674 | 1745-3682 |
|  39 |  3 |  3 | Acta orthopaedica                            | Acta orthopaedica. Supplementum              | 1745-3690 | 1745-3704 | 1745-3674 | 1745-3682 |
|  40 |  3 |  3 | Acta orthopaedica                            | Acta orthopaedica. Supplementum              | 1745-3690 | 1745-3704 | 1745-3674 | 1745-3682 |
|  41 |  3 |  3 | Acta orthopaedica                            | Acta orthopaedica. Supplementum              | 1745-3690 | 1745-3704 | 1745-3674 | 1745-3682 |
|  42 |  3 |  3 | Acta orthopaedica                            | Acta orthopaedica. Supplementum              | 1745-3690 | 1745-3704 | 1745-3674 | 1745-3682 |
|  43 |  3 |  3 | Acta orthopaedica                            | Acta orthopaedica. Supplementum              | 1745-3690 | 1745-3704 | 1745-3674 | 1745-3682 |
|  44 |  0 |  3 | Acta orthopaedica                            | Acta orthopaedica. Supplementum              | 1745-3690 | 1745-3704 | 1745-3674 | 1745-3682 |
|  52 |  3 |  3 | ACTA PSYCHIATRICA SCANDINAVICA. Supplementum | Acta Psychiatrica Scandinavica               | 1600-0447 | 0001-690X | 0065-1591 | 1600-5473 |
|  53 |  1 |  3 | ACTA PSYCHIATRICA SCANDINAVICA. Supplementum | Acta Psychiatrica Scandinavica               | 1600-0447 | 0001-690X | 0065-1591 | 1600-5473 |
| 107 |  3 |  3 | American Economic Journal. Economic Policy   | American Economic Journal: Applied Economics | 1945-7790 | 1945-7782 | 1945-7731 | 1945-774X |
| 108 |  3 |  3 | American Economic Journal. Macroeconomics    | American Economic Journal: Applied Economics | 1945-7790 | 1945-7782 | 1945-7707 | 1945-7715 |
| 109 |  3 |  3 | American Economic Journal. Microeconomics    | American Economic Journal: Applied Economics | 1945-7790 | 1945-7782 | 1945-7669 | 1945-7685 |
| 110 |  3 |  3 | American Economic Journal: Economic Policy   | American Economic Journal: Applied Economics | 1945-7790 | 1945-7782 | 1945-7731 | 1945-774X |
| 111 |  3 |  3 | American Economic Journal: Economic Policy   | American Economic Journal: Applied Economics | 1945-7790 | 1945-7782 | 1945-7731 | 1945-774X |
| 112 |  3 |  3 | American Economic Journal: Macroeconomics    | American Economic Journal: Applied Economics | 1945-7790 | 1945-7782 | 1945-7707 | 1945-7715 |
| 113 |  3 |  3 | American Economic Journal: Macroeconomics    | American Economic Journal: Applied Economics | 1945-7790 | 1945-7782 | 1945-7707 | 1945-7715 |
| 114 |  3 |  3 | American Economic Journal: Microeconomics    | American Economic Journal: Applied Economics | 1945-7790 | 1945-7782 | 1945-7669 | 1945-7685 |
| 115 |  3 |  3 | American Economic Journal: Microeconomics    | American Economic Journal: Applied Economics | 1945-7790 | 1945-7782 | 1945-7669 | 1945-7685 |
| 116 |  3 |  3 | American Economic Journal. Applied Economics | American Economic Journal: Economic Policy   | 1945-774X | 1945-7731 | 1945-7782 | 1945-7790 |
| 117 |  3 |  3 | American Economic Journal. Macroeconomics    | American Economic Journal: Economic Policy   | 1945-774X | 1945-7731 | 1945-7707 | 1945-7715 |
| 118 |  3 |  3 | American Economic Journal. Microeconomics    | American Economic Journal: Economic Policy   | 1945-774X | 1945-7731 | 1945-7669 | 1945-7685 |
| 119 |  3 |  3 | American Economic Journal: Applied Economics | American Economic Journal: Economic Policy   | 1945-774X | 1945-7731 | 1945-7782 | 1945-7790 |
| 120 |  3 |  3 | American Economic Journal: Applied Economics | American Economic Journal: Economic Policy   | 1945-774X | 1945-7731 | 1945-7782 | 1945-7790 |
| 121 |  3 |  3 | American Economic Journal: Macroeconomics    | American Economic Journal: Economic Policy   | 1945-774X | 1945-7731 | 1945-7707 | 1945-7715 |
| 122 |  3 |  3 | American Economic Journal: Macroeconomics    | American Economic Journal: Economic Policy   | 1945-774X | 1945-7731 | 1945-7707 | 1945-7715 |
| 123 |  3 |  3 | American Economic Journal: Microeconomics    | American Economic Journal: Economic Policy   | 1945-774X | 1945-7731 | 1945-7669 | 1945-7685 |
| 124 |  3 |  3 | American Economic Journal: Microeconomics    | American Economic Journal: Economic Policy   | 1945-774X | 1945-7731 | 1945-7669 | 1945-7685 |
| 125 |  3 |  3 | American Economic Journal. Applied Economics | American Economic Journal: Macroeconomics    | 1945-7715 | 1945-7707 | 1945-7782 | 1945-7790 |
| 126 |  3 |  3 | American Economic Journal. Economic Policy   | American Economic Journal: Macroeconomics    | 1945-7715 | 1945-7707 | 1945-7731 | 1945-774X |
| 127 |  3 |  3 | American Economic Journal. Microeconomics    | American Economic Journal: Macroeconomics    | 1945-7715 | 1945-7707 | 1945-7669 | 1945-7685 |
| 128 |  3 |  3 | American Economic Journal: Applied Economics | American Economic Journal: Macroeconomics    | 1945-7715 | 1945-7707 | 1945-7782 | 1945-7790 |
| 129 |  3 |  3 | American Economic Journal: Applied Economics | American Economic Journal: Macroeconomics    | 1945-7715 | 1945-7707 | 1945-7782 | 1945-7790 |
| 130 |  3 |  3 | American Economic Journal: Economic Policy   | American Economic Journal: Macroeconomics    | 1945-7715 | 1945-7707 | 1945-7731 | 1945-774X |
| 131 |  3 |  3 | American Economic Journal: Economic Policy   | American Economic Journal: Macroeconomics    | 1945-7715 | 1945-7707 | 1945-7731 | 1945-774X |
| 132 |  3 |  3 | American Economic Journal: Microeconomics    | American Economic Journal: Macroeconomics    | 1945-7715 | 1945-7707 | 1945-7669 | 1945-7685 |
| 133 |  3 |  3 | American Economic Journal: Microeconomics    | American Economic Journal: Macroeconomics    | 1945-7715 | 1945-7707 | 1945-7669 | 1945-7685 |
| 134 |  3 |  3 | American Economic Journal. Applied Economics | American Economic Journal: Microeconomics    | 1945-7685 | 1945-7669 | 1945-7782 | 1945-7790 |
| 135 |  3 |  3 | American Economic Journal. Economic Policy   | American Economic Journal: Microeconomics    | 1945-7685 | 1945-7669 | 1945-7731 | 1945-774X |
| 136 |  3 |  3 | American Economic Journal. Macroeconomics    | American Economic Journal: Microeconomics    | 1945-7685 | 1945-7669 | 1945-7707 | 1945-7715 |
| 137 |  3 |  3 | American Economic Journal: Applied Economics | American Economic Journal: Microeconomics    | 1945-7685 | 1945-7669 | 1945-7782 | 1945-7790 |
| 138 |  3 |  3 | American Economic Journal: Applied Economics | American Economic Journal: Microeconomics    | 1945-7685 | 1945-7669 | 1945-7782 | 1945-7790 |
| 139 |  3 |  3 | American Economic Journal: Economic Policy   | American Economic Journal: Microeconomics    | 1945-7685 | 1945-7669 | 1945-7731 | 1945-774X |
| 140 |  3 |  3 | American Economic Journal: Economic Policy   | American Economic Journal: Microeconomics    | 1945-7685 | 1945-7669 | 1945-7731 | 1945-774X |
| 141 |  3 |  3 | American Economic Journal: Macroeconomics    | American Economic Journal: Microeconomics    | 1945-7685 | 1945-7669 | 1945-7707 | 1945-7715 |
| 142 |  3 |  3 | American Economic Journal: Macroeconomics    | American Economic Journal: Microeconomics    | 1945-7685 | 1945-7669 | 1945-7707 | 1945-7715 |
+-----+----+----+----------------------------------------------+----------------------------------------------+-----------+-----------+-----------+-----------+

... indicates that one row in the dataset, the row where base_report_id=44, needs to be upodated. We agree?

And if the full dataset returns zero rows (as the sample dataset does) on this query ...

select b.title,b.title_sci,b.issn1,b.issn2,b.issn,b.eissn,b.databas,count(*) as N
from base_report_gt_zero 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;

... then the update logic can be further simplified because the join on (title_sci, title, issn1,issn2, issn, eissn, databas) must always produce exactly one master_data row per base_report row, so the update statement replacing your sproc would be ...

update base_report_gt_zero b
join master_data m using(title,title_sci,issn1,issn2,issn,eissn,databas)
set b.cc=m.cc
where b.cc=0 and b.cc <> m.cc;

On the sample dataset, that query updates the sample dataset row for base_report_id=44, and none other, which is the desired result, correct?



Edited 1 time(s). Last edit at 09/26/2017 03:36PM by Peter Brawley.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Display all the records from table using cursor
665
September 26, 2017 03:35PM


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.