At 10:55 AM 11/1/2012 +0800, Mark Tompsett wrote:
Greetings, Read the manuals, please: <http://dev.mysql.com/doc/refman/5.5/en/update.html>http://dev.mysql.com/doc/refman/5.5/en/update.html http://dev.mysql.com/doc/refman/5.5/en/join.html (you could look at 5.6 or 5.1 too accordingly)
Many thanks Mark. I had in fact tried that (my previous email: "I have tried various combinations of LEFT|RIGHT JOIN biblio ON (biblioitems.biblionumber = biblio.biblionumber) but with no success so far" but your "join biblioitems on biblio.biblionumber=biblioitems.biblionumber" is good. My last 3-day course with Oracle was 10 years ago :/ For the record (Koha 3.8.5, MySQL 5.5.24): mysql> UPDATE biblio RIGHT JOIN biblioitems ON biblio.biblionumber=biblioitems.biblionumber SET biblio.author=ExtractValue(biblioitems.marcxml,'//datafield[@tag="100"]/subfield[@code>="a"]') WHERE biblio.author IS NULL; is fully functional. So far I have only done it on the sandbox; all previous "reports" that relied on biblio.author are now working. All that remains is to work out why our cataloguers cannot get the 100$a into the correct column, so that I don't have to go through this exercise every day. I'll also look into whether this has cured our authority problem, and whether or not NULL!=space(s) affects anything (or replace space[s] with NULLs.) Again many thanks, Paul --- Maritime heritage and history, preservation and conservation, research and education through the written word and the arts. <http://NavalMarineArchive.com> and <http://UltraMarine.ca>