Marks_2017.csv
Name, class, Marks
"a1", 9, '{"Halfyr_T" : "45", "Halfyr_P" : "35", "Annual_T" : "42", "Annual_P" : "40"}'
"a2", 9, '{"Halfyr_T" : "45", "Halfyr_P" : "35", "Annual_T" : "42", "Annual_P" : "40"}'
"a3", 9, '{"Halfyr_T" : "45", "Halfyr_P" : "35", "Annual_T" : "42", "Annual_P" : "40"}'
"b1", 10, '{"Halfyr_T" : "45", "Halfyr_P" : "35", "Annual_T" : "42", "Annual_P" : "40"}'
"b2", 10, '{"Halfyr_T" : "45", "Halfyr_P" : "35", "Annual_T" : "42", "Annual_P" : "40"}'
"b3", 10, '{"Halfyr_T" : "45", "Halfyr_P" : "35", "Annual_T" : "42", "Annual_P" : "40"}'
Marks_2017_Update.csv
Name, class, Marks
"a1", 9, '{"Halfyr_T" : "-1", "Halfyr_P" : "-1", "Annual_T" : "-1", "Annual_P" : "-1", "Assignment" : "0"}'
"a2", 9, '{"Halfyr_T" : "-1", "Halfyr_P" : "-1", "Annual_T" : "-1", "Annual_P" : "-1", "Assignment" : "0"}'
"a3", 9, '{"Halfyr_T" : "-1", "Halfyr_P" : "-1", "Annual_T" : "-1", "Annual_P" : "-1", "Assignment" : "0"}'
"b1", 10, '{"Halfyr_T" : "0", "Halfyr_P" : "-1", "Annual_T" : "-1", "Annual_P" : "-1", "Assignment" : "0", "Quiz" : "-1"}'
"b2", 10, '{"Halfyr_T" : "0", "Halfyr_P" : "-1", "Annual_T" : "-1", "Annual_P" : "-1", "Assignment" : "0", "Quiz" : "-1"}'
"b3", 10, '{"Halfyr_T" : "0", "Halfyr_P" : "-1", "Annual_T" : "-1", "Annual_P" : "-1", "Assignment" : "0", "Quiz" : "-1"}'
"c1", 8, '{"Halfyr_T" : "0", "Halfyr_P" : "0", "Annual_T" : "0", "Annual_P" : "0", "Assignment" : "-1"}'
"c2", 8, '{"Halfyr_T" : "0", "Halfyr_P" : "0", "Annual_T" : "0", "Annual_P" : "0", "Assignment" : "-1"}'
"c3", 8, '{"Halfyr_T" : "0", "Halfyr_P" : "0", "Annual_T" : "0", "Annual_P" : "0", "Assignment" : "-1"}'
Reference solution am trying to follow:
MySQL LOAD DATA INFILE with ON DUPLICATE KEY UPDATE
-> key is name_class
-> During insert into Marks_2017 (ON DUPLICATE KEY), using Marks_2017_Update, if duplicate key is found
For new json_key in Marks_2017_update, irrespective of "0"/"-1" key value, append those new json_keys and json_value set to "0". (For ex:- Assignment for class 9 and Assignment and Quiz for class 10, Set to "0" in Marks_2017)
if json_value is "-1" don't change the json_value in Marks_2017 (if json_key is already present).
Reset Marks_2017 json_key value to "0" if Marks_2017_update json_key value is "0" for existing key (i.e. reset marks of class 10, Halfyr_T to "0" in Marks_2017 as value is "0" for class 10, Halfyr_T in Marks_2017_Update)
-> For new key, insert records to Marks_2017 with Default value "0". (Ex:- For class 8, Assignment in Marks_2017_Update, value is "-1", Still, in Marks_2017 it shud be inserted with "0", As it is a new record.)
Am majorly facing issue while checking json_key and updating its values.
Tried using json_extract, json_set and json_merge. but unable to resolve my requirement.
-- creating db
CREATE SCHEMA IF NOT EXISTS `allmarks` DEFAULT CHARACTER SET utf8 ;
USE `allmarks` ;
-- table schema as per csv file
CREATE TABLE IF NOT EXISTS Marks_2017 (
`Name` VARCHAR(255) NOT NULL,
`class` INT(2) NOT NULL,
`Marks` JSON NOT NULL,
UNIQUE INDEX `Marks_2017_index` (`Name` ASC, `class` ASC));
-- Paste file in the above output path SHOW VARIABLES LIKE "secure_file_priv";
-- importing csv_file data to table
LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/Marks_2017.csv'
INTO TABLE Marks_2017
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\''
LINES TERMINATED BY '\r\n'
IGNORE 1 ROWS;
-- importing csv_file data to update table for updating master data
CREATE TEMPORARY TABLE Marks_2017_Update SELECT * FROM Marks_2017 WHERE 1=0; -- where 1=0 will drop all the existing indexes
LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/Marks_2017_Update.csv'
INTO TABLE Marks_2017_Update
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\''
LINES TERMINATED BY '\r\n'
IGNORE 1 ROWS;
