From ef1203021dc3e95ba7ba3e15e6d84edf23c4f674 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Fri, 7 May 2021 09:22:50 +0200 Subject: [PATCH] Bug 14237: Set course_items.biblionumber in the DBrev We need to create the new column without the NOT NULL clause, set the correct values, then add the NOT NULL clause. Signed-off-by: Nick Clemens --- .../bug_14237-add_course_items.biblionumber_column.perl | 12 +++++++++++- 1 file changed, 11 insertions(+), 1 deletion(-) diff --git a/installer/data/mysql/atomicupdate/bug_14237-add_course_items.biblionumber_column.perl b/installer/data/mysql/atomicupdate/bug_14237-add_course_items.biblionumber_column.perl index 185c67a213..a6e75bfe70 100644 --- a/installer/data/mysql/atomicupdate/bug_14237-add_course_items.biblionumber_column.perl +++ b/installer/data/mysql/atomicupdate/bug_14237-add_course_items.biblionumber_column.perl @@ -1,7 +1,17 @@ $DBversion = 'XXX'; if( CheckVersion( $DBversion ) ){ unless( column_exists( 'course_items', 'biblionumber') ) { - $dbh->do(q{ ALTER TABLE course_items ADD `biblionumber` int(11) NOT NULL AFTER `itemnumber` }); + $dbh->do(q{ ALTER TABLE course_items ADD `biblionumber` int(11) AFTER `itemnumber` }); + + $dbh->do(q{ + UPDATE course_items + LEFT JOIN items ON items.itemnumber=course_items.itemnumber + SET course_items.biblionumber=items.biblionumber + WHERE items.itemnumber IS NOT NULL + }); + + $dbh->do(q{ ALTER TABLE course_items MODIFY COLUMN `biblionumber` INT(11) NOT NULL }); + $dbh->do(q{ ALTER TABLE course_items ADD CONSTRAINT `fk_course_items_biblionumber` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE }); $dbh->do(q{ ALTER TABLE course_items CHANGE `itemnumber` `itemnumber` int(11) DEFAULT NULL }); } -- 2.11.0