Hi Stephano, Thanks very much for this. I will test later tonight. For some background, I recently uploaded the Library Of Congress Subject authority file (2014 release), which is now available in Marc8, UTF-8, and XML formats as an open access resource: https://www.loc.gov/cds/products/marcDist.php (about 415,000 records). For some reason, about 16,600 duplicates were also uploaded (minus any Genre/Form terms which can be literal duplicates of Topical Terms). I needed a way to isolate these duplicate IDs and remove them.
I actually ran the following report from SQL reports list: https://wiki.koha-community.org/wiki/SQL_Reports_Library#Duplicate_authorities, and simply changed 035 to 010. The report generated a column of all IDs that have been duplicated. I then took these values, went into phpmyadmin and ran "DELETE FROM auth_header WHERE authid IN ({all duplicate tag 010 IDs separated by commas}), then reindexed Zebra. I then ran the link_bibs_to_authorities.pl script that comes with Koha to ensure any unlinked bibs were relinked to authorities. My authority file is clean now. I still don't have an explanation as to why or how the dupes entered into the upload. I'll bookmark your report though and tinker with it when I get some quiet time. I appreciate your help! Craig Butosi, MA, MLIS, B Mus (Hons) Library: library.craigbutosi.ca On Tue, May 14, 2019 at 4:55 AM Stefano Bargioni <bargi...@pusc.it> wrote: > Maybe a self join can help you. > See https://www.w3schools.com/sql/sql_join_self.asp < > https://www.w3schools.com/sql/sql_join_self.asp> > However, your query could be very slow, since the match will not work on > indexed data. If not sure, but try > > SELECT A.authid AS authid1, B.authid AS authid2, > ExtractValue(A.marcxml,'/record/datafield[@tag="010"]/subfield[@code="a"]') > AS dup > FROM auth_header A, auth_header B > WHERE A.authid <> B.authid > AND > ExtractValue(A.marcxml,'/record/datafield[@tag="010"]/subfield[@code="a"]') > <> '' > AND > ExtractValue(A.marcxml,'/record/datafield[@tag="010"]/subfield[@code="a"]') > = > ExtractValue(B.marcxml,'/record/datafield[@tag="010"]/subfield[@code="a"]') > ORDER BY A.authid; > > HTH. Stefano > > > Subject: [Koha] SQL Report - Authorities. List duplicate values based > > on 010$a > > Message-ID: > > < > cap4tgf9em5-4ximherf9b6ss+9ogzmh8_imgkz-zjdtdjeb...@mail.gmail.com> > > Content-Type: text/plain; charset="UTF-8" > > > > Hi all, > > > > Koha 18.11 on Ubunti 16.04 > > > > May I request help for an SQL report that does the following, please: > > > > Identify and list by authid and main heading all authority records that > > have a tag 010$a with duplicate values? > > > > I am attempting to located all authority records that can be merged (or > > de-duped via merge). > > > > Many thanks! > > > > Craig Butosi, MA, MLIS, B Mus (Hons) > > Library: library.craigbutosi.ca > > > > > > ------------------------------ > > > > Message: 2 > > Date: Sun, 12 May 2019 01:49:55 +0530 > > From: vijay kumar <vijcrj...@gmail.com> > > To: koha@lists.katipo.co.nz > > Subject: [Koha] upgrade from koha-17.05.06 to koha-latest > > Message-ID: > > < > cacnt1h6pkrzsrjiqqseoevwdbumyrwdmxcoimbkswesfpub...@mail.gmail.com> > > Content-Type: text/plain; charset="UTF-8" > > > > Dear all, > > We have koha-17.05.06, installed using tarball method. > > > > I have followed the following methods for upgrading from koha-17.05.06 to > > koha-latest > > 1. Updated koha-list source accordingly koha-stable > > 2. Update all perl dependencies and cpan module > > > > vijay@debian:/usr/share/koha-18.11.04$ perl Makefile.PL > > /usr/share/koha-17.05.06/misc/koha-install-log > > /usr/share/koha/misc/koha-install-log > > > > and also > > > > vijay@debian:/usr/share/koha-18.11.04$ sudo perl Makefile.PL > > /usr/share/koha-17.05.06/misc/koha-install-log > > /usr/share/koha-18.11.04/misc/koha-install-log > > > > vijay@debian:/usr/share/koha-18.11.04$ sudo make > > > > vijay@debian:/usr/share/koha-18.11.04$ sudo make upgrade > > > ------------------------------------------------------------------------------------ > > > > Getting the following error during upgrade > > Kindly suggest the proper method for up-gradation of koha-latest > > > > With regards, > > Vijay Kumar > > > > > > ::::error:::: > > > > vijay@debian:/usr/share/koha-18.11.04$ sudo make upgrade > > Can't locate C4/Installer/UpgradeBackup.pm in @INC (you may need to > install > > the C4::Installer::UpgradeBackup module) (@INC contains: /etc/perl > > /usr/local/lib/x86_64-linux-gnu/perl/5.24.1 /usr/local/share/perl/5.24.1 > > /usr/lib/x86_64-linux-gnu/perl5/5.24 /usr/share/perl5 > > /usr/lib/x86_64-linux-gnu/perl/5.24 /usr/share/perl/5.24 > > /usr/local/lib/site_perl /usr/lib/x86_64-linux-gnu/perl-base). > > BEGIN failed--compilation aborted. > > Makefile:22908: recipe for target 'make_upgrade_backup' failed > > make: *** [make_upgrade_backup] Error 2 > > > > > > ------------------------------ > > > > Message: 3 > > Date: Sun, 12 May 2019 08:27:33 +1200 > > From: David Nind <david.n...@gmail.com> > > To: Craig Butosi <cbut...@gmail.com> > > Cc: koha <koha@lists.katipo.co.nz> > > Subject: Re: [Koha] SQL Report - Authorities. List duplicate values > > based on 010$a > > Message-ID: > > < > cak5ompcjclgvyohfbubz2-et9-tf-ctwyk+ui21eypxhxe-...@mail.gmail.com> > > Content-Type: text/plain; charset="UTF-8" > > > > Hi Craig. > > > > I'm not that great at reports, but you might be able to find something in > > the SQL reports library that could help: > > https://wiki.koha-community.org/wiki/SQL_Reports_Library > > > > > > David Nind | david.n...@gmail.com > > PO Box 12367, Thorndon, Wellington, New Zealand 6144 > > m. +64 21 0537 847 > > > > > > On Sun, 12 May 2019 at 05:46, Craig Butosi <cbut...@gmail.com> wrote: > > > >> Hi all, > >> > >> Koha 18.11 on Ubunti 16.04 > >> > >> May I request help for an SQL report that does the following, please: > >> > >> Identify and list by authid and main heading all authority records that > >> have a tag 010$a with duplicate values? > >> > >> I am attempting to located all authority records that can be merged (or > >> de-duped via merge). > >> > >> Many thanks! > >> > >> Craig Butosi, MA, MLIS, B Mus (Hons) > >> Library: library.craigbutosi.ca > >> _______________________________________________ > >> Koha mailing list http://koha-community.org > >> Koha@lists.katipo.co.nz > >> https://lists.katipo.co.nz/mailman/listinfo/koha > >> > > > > > > ------------------------------ > > > > Subject: Digest Footer > > > > _______________________________________________ > > Koha mailing list > > Koha@lists.katipo.co.nz > > https://lists.katipo.co.nz/mailman/listinfo/koha > > > > > > ------------------------------ > > > > End of Koha Digest, Vol 163, Issue 12 > > ************************************* > > _______________________________________________ > Koha mailing list http://koha-community.org > Koha@lists.katipo.co.nz > https://lists.katipo.co.nz/mailman/listinfo/koha > _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
