> 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.