cropname.sql 1.04 KB
Newer Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

CREATE TABLE `cropname` (
  `cropId` bigint(20) NOT NULL,
  `otherName` varchar(255) NOT NULL,
  UNIQUE KEY `UK_o6svkv9o98t67jnnxv7i0d4a7` (`otherName`),
  KEY `FK_tb67bwitjd2dlmmfbxqsfcjmq` (`cropId`),
  CONSTRAINT `FK_tb67bwitjd2dlmmfbxqsfcjmq` FOREIGN KEY (`cropId`) REFERENCES `crop` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

alter table accession add `cropName` varchar(50) DEFAULT NULL,
  add `cropId` bigint(20) DEFAULT NULL, 
  add CONSTRAINT `FK_826wi40aq5ucsmeki61dnr9pt` FOREIGN KEY (`cropId`) REFERENCES `crop` (`id`);

alter table accessionhistoric add `cropName` varchar(50) DEFAULT NULL,
  add `cropId` bigint(20) DEFAULT NULL, 
  add CONSTRAINT `FK_tb12h2n6q2ck6wvpp2l73up2a` FOREIGN KEY (`cropId`) REFERENCES `crop` (`id`);

update accession a inner join croptaxonomy ct on ct.taxonomyId=a.taxonomyId2 inner join crop c on c.id=ct.cropId set a.cropName=c.shortName, a.cropId=c.id;
update accessionhistoric a inner join croptaxonomy ct on ct.taxonomyId=a.taxonomyId2 inner join crop c on c.id=ct.cropId set a.cropName=c.shortName, a.cropId=c.id;