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/join.html
(you could look at 5.6 or 5.1 too accordingly)
mysql> select count(*) from biblio;
+----------+
| count(*) |
+----------+
| 38497 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from biblio where author is NULL;
+----------+
| count(*) |
+----------+
| 8967 |
+----------+
1 row in set (0.00 sec)
mysql> select
biblio.author,ExtractValue(marcxml,'//datafield[@tag="100"]/subfield[@code>="a"]')
from biblioitems left join biblio on
biblio.biblionumber=biblioitems.biblionumber where biblio.author is NULL;
+--------+----------------------------------------------------------------------+
| author | ExtractValue(marcxml,'//datafield[@tag="100"]/subfield[@code>="a"]')
|
+--------+----------------------------------------------------------------------+
| NULL |
|
| NULL |
|
...
| NULL |
|
| NULL |
|
+--------+----------------------------------------------------------------------+
8967 rows in set (0.89 sec)
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;
Query OK, 8967 rows affected (1.68 sec)
Rows matched: 8967 Changed: 8967 Warnings: 0
mysql> select count(*) from biblio where author is NULL;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
Granted, they are all blank still, because all my records had nothing in the
100$a column for those records.
I don’t know if that will or will not affect any logic in Koha, as
NULL!=space(s). Though you could go back and replace with NULL for things with
no printable characters in it, I’m sure.
GPML,
Mark Tompsett
_______________________________________________
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/