Would an SQL Update statement be more effective here? Like: UPDATE biblio SET author = ExtractValue(marcxml,'//datafield[@tag="100"]/subfield[@code>="a"]') WHERE author IS NULL
On Wed, Oct 31, 2012 at 1:00 PM, Paul <pau...@aandc.org> wrote: > At 08:09 PM 10/29/2012 +0100, you wrote: > >> Hi Paul, >>> >> [snip] >> >> For your reports you always have the option to query the MARCXML data >>> directly, nothing should be lost. >>> >> >> I agree -- in fact for staff, I've already written a new report replacing >> biblio.author with >> ExtractValue(marcxml,'//**datafield[@tag="100"]/**subfield[@code>="a"]') >> and it works perfectly. >> >> But this doesn't "correct" the MySQL db. Where/why on earth has it gone >> wrong, and how to correct it? >> > > Could some kind soul with knowledge of using XML in MySQL assist? > > I'm trying to copy the XML 100$a to biblio.author where this latter is > NULL, but: > > mysql> INSERT INTO biblio (biblio.author) > -> SELECT > ExtractValue(marcxml,'//**datafield[@tag="100"]/**subfield[@code>="a"]') > FROM biblioitems > -> WHERE biblio.author IS NULL; > ERROR 1054 (42S22): Unknown column 'biblio.author' in 'where clause' > > so I tried to be more explicit for biblio.author and now the XML has an > error > > mysql> INSERT INTO biblio (biblio.author) > -> SELECT author FROM biblio, ExtractValue(marcxml,'//** > datafield[@tag="100"]/**subfield[@code>="a"]') FROM biblioitems > -> WHERE biblio.author IS NULL; > ERROR 1064 (42000): You have an error in your SQL syntax; check the manual > that corresponds to your MySQL server version for the right syntax to use > near '(marcxml,'//datafield[@tag="**100"]/subfield[@code>="a"]') FROM > biblioitems > WHERE' at line 2 > > I have tried various combinations of LEFT|RIGHT JOIN biblio ON ( > biblioitems.biblionumber = biblio.biblionumber) but with no success so far. > > > Thanks and regards -- Paul > > > > > > > > Hope that helps, >> >> Katrin >> >> >> -----Ursprüngliche Nachricht----- >> Von: >> koha-devel-boun...@lists.koha-**community.org<koha-devel-boun...@lists.koha-community.org>im >> Auftrag von Paul >> Gesendet: Mo 29.10.2012 19:55 >> An: >> koha-de...@lists.koha-**community.org<koha-devel@lists.koha-community.org> >> Betreff: [Koha-devel] Losing biblio.author in 3.8.5 >> >> We have a [rather important, it's for donors' tax receipts] report that >> includes: >> >> SELECT >> items.barcode AS Barcode,items.dateaccessioned AS 'Acc Date', >> biblio.title AS Title, biblio.author AS Author,biblioitems.**publishercode >> AS >> Publisher,biblioitems.**publicationyear AS Year, >> biblioitems.editionstatement AS Edition, >> items.price as FMV, etc etc etc >> >> It worked perfectly from 3.6.1 up to and including 3.8.4 >> >> Since we upgraded to 3.8.5 (24 Sep, to fix bug 8520), something has >> changed >> -- biblio.author systematically comes up empty (all 245$a fields are >> present) while *all* the other field are perfect. (added later: just >> checked some other reports that use biblio.author -- same result, other >> fields are good, author fails.) >> >> I've looked around release notes and bugs without finding anything >> relevant, and am now at a loss as to what I should look for. >> >> Any suggestions warmly accepted. >> >> Thanks and regards, >> Paul >> >> ______________________________**_________________ >> Koha-devel mailing list >> koha-de...@lists.koha-**community.org<Koha-devel@lists.koha-community.org> >> http://lists.koha-community.**org/cgi-bin/mailman/listinfo/**koha-devel<http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel> >> website : http://www.koha-community.org/ >> git : http://git.koha-community.org/ >> bugs : http://bugs.koha-community.**org/<http://bugs.koha-community.org/> >> > > --- > Maritime heritage and history, preservation and conservation, > research and education through the written word and the arts. > <http://NavalMarineArchive.com**> and <http://UltraMarine.ca> > > ______________________________**_________________ > Koha-devel mailing list > koha-de...@lists.koha-**community.org<Koha-devel@lists.koha-community.org> > http://lists.koha-community.**org/cgi-bin/mailman/listinfo/**koha-devel<http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel> > website : http://www.koha-community.org/ > git : http://git.koha-community.org/ > bugs : http://bugs.koha-community.**org/ <http://bugs.koha-community.org/> > > > ______________________________**_________________ > Koha-devel mailing list > koha-de...@lists.koha-**community.org<Koha-devel@lists.koha-community.org> > http://lists.koha-community.**org/cgi-bin/mailman/listinfo/**koha-devel<http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel> > website : http://www.koha-community.org/ > git : http://git.koha-community.org/ > bugs : http://bugs.koha-community.**org/ <http://bugs.koha-community.org/> >
_______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
