SetCopyrightDate.sql

UPDATE biblio b JOIN biblioitems bi ON b.biblionumber = bi.biblionumber 
SET b.copyrightdate = SUBSTRING(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ExtractValue(bi.marcxml, '//datafield[@tag="264"]/subfield[@code="c"]'), "[", ""), "]", ""), "?", ""), "c", ""), "©", ""), "℗", ""), -4)
WHERE copyrightdate IS NULL AND frameworkcode = "KN";

SELECT b.frameworkcode, b.biblionumber, b.copyrightdate, 
SUBSTRING(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ExtractValue(bi.marcxml, '//datafield[@tag="264"]/subfield[@code="c"]'), "[", ""), "]", ""), "?", ""), "c", ""), "©", ""),"℗", ""), -4) as year
FROM biblio b JOIN biblioitems bi ON b.biblionumber = bi.biblionumber 
WHERE copyrightdate IS NULL
LIMIT 10;

SELECT frameworkcode, count(*) FROM biblio WHERE copyrightdate IS NULL GROUP BY frameworkcode;
SELECT frameworkcode, count(*) FROM biblio WHERE copyrightdate = 0 GROUP BY frameworkcode;

UPDATE biblio SET copyrightdate = NULL WHERE copyrightdate = 0;