At 02:27 PM 10/31/2012 -0600, Mike Hafen wrote:
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
Many thanks for the suggestion, but I ran into problems -- I think it's to
do with mixing a straightforward 'table/column' as in biblio.author with an
'ExtractValue' from a different table.
Here's your suggestion:
mysql> UPDATE biblio SET author =
ExtractValue(marcxml,'//datafield[@tag="100"]/subfield[@code>="a"]') WHERE
author IS NULL;
ERROR 1054 (42S22): Unknown column 'marcxml' in 'field list'
So I tried a second FROM to point to the 'table/column' with marcxml FROM
biblioitems:
mysql> UPDATE biblio SET author =
ExtractValue(marcxml,'//datafield[@tag="100"]/subfield[@code>="a"]') FROM
biblioitems WHERE 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 'FROM biblioitems WHERE author IS NULL' at line 1
No joy; so bracket the 'ExtractValue' with the table in front of it:
mysql> UPDATE biblio SET author =
(biblioitems(ExtractValue(marcxml,'//datafield[@tag="100"]/subfield[@code>="a"]'))
WHERE author IS NULL;
ERROR 1305 (42000): FUNCTION koha384.biblioitems does not exist
MySQL reads that as a FUNCTION, so put biblioitems. inside the bracket:
mysql> UPDATE biblio SET author =
ExtractValue(biblioitems.marcxml,'//datafield[@tag="100"]/subfield[@code>="a"]')
WHERE author IS NULL;
ERROR 1054 (42S22): Unknown column 'biblioitems.marcxml' in 'field list'
MySQL is really touchy ;={
I hate bothering you, but do you have thoughts on this?
Thanks - Paul
On Wed, Oct 31, 2012 at 1:00 PM, Paul
<<mailto:pau...@aandc.org>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:
<mailto: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:
<mailto:koha-devel@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
<mailto:Koha-devel@lists.koha-community.org>Koha-devel@lists.koha-community.org
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel
website : <http://www.koha-community.org/>http://www.koha-community.org/
git : <http://git.koha-community.org/>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>http://NavalMarineArchive.com> and
<<http://UltraMarine.ca>http://UltraMarine.ca>
_______________________________________________
Koha-devel mailing list
<mailto:Koha-devel@lists.koha-community.org>Koha-devel@lists.koha-community.org
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel
website : <http://www.koha-community.org/>http://www.koha-community.org/
git : <http://git.koha-community.org/>http://git.koha-community.org/
bugs : <http://bugs.koha-community.org/>http://bugs.koha-community.org/
_______________________________________________
Koha-devel mailing list
<mailto:Koha-devel@lists.koha-community.org>Koha-devel@lists.koha-community.org
http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel
website : <http://www.koha-community.org/>http://www.koha-community.org/
git : <http://git.koha-community.org/>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/
---
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-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/
