booleans.sql 1.84 KB
Newer Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
-- Availability
select Availability, count(*) from accession group by availability;

update accession set Availability=0 where Availability='N';
update accession set Availability=1 where Availability='Y';
update accession set Availability=null where Availability not in ('0', '1');

ALTER TABLE `accession` CHANGE COLUMN `Availability` `Availability` BIT NULL DEFAULT NULL COMMENT 'Available for exchange'  ;


-- MLS_Status
select MLS_Status, count(*) from accession group by MLS_Status;

ALTER TABLE `accession` CHANGE COLUMN `MLS_Status` `MLS_Status` char(1) NULL DEFAULT NULL COMMENT 'In MLS'  ;

update accession set MLS_Status=0 where MLS_Status='N';
update accession set MLS_Status=1 where MLS_Status='Y';
update accession set MLS_Status=null where MLS_Status not in ('0', '1');

ALTER TABLE `accession` CHANGE COLUMN `MLS_Status` `MLS_Status` BIT NULL DEFAULT NULL COMMENT 'In MLS'  ;

-- In_Svalbard
select In_Svalbard, count(*) from accession group by In_Svalbard;

ALTER TABLE `accession` CHANGE COLUMN `In_Svalbard` `In_Svalbard` char(1) NULL DEFAULT NULL COMMENT 'In Svalbard'  ;

update accession set In_Svalbard=0 where In_Svalbard='N';
update accession set In_Svalbard=1 where In_Svalbard='Y';
update accession set In_Svalbard=null where In_Svalbard not in ('0', '1');

ALTER TABLE `accession` CHANGE COLUMN `In_Svalbard` `In_Svalbard` BIT NULL DEFAULT NULL COMMENT 'In Svalbard'  ;


-- In_Trust
select In_Trust, count(*) from accession group by In_Trust;

ALTER TABLE `accession` CHANGE COLUMN `In_Trust` `In_Trust` char(1) NULL DEFAULT NULL COMMENT 'Article 15 accession'  ;

update accession set In_Trust=0 where In_Trust='N';
update accession set In_Trust=1 where In_Trust='Y';
update accession set In_Trust=null where In_Trust not in ('0', '1');

ALTER TABLE `accession` CHANGE COLUMN `In_Trust` `In_Trust` BIT NULL DEFAULT NULL COMMENT 'Article 15 accession'  ;