I had the need to populate data in a database table after a migration to update the production database schema. I thought of different means to achieve this. Manually populating the rows with data was not an option because the database contains thousands of records.

I considered two approaches:

• Using a regex
• Writing code to achieve it

After a while, I settled for the second approach because of access restrictions on running the regex.

Here is how I achieved it:

• I exported the data,
• Wrote the JavaScript code to generate the content of the new column
• Created a temporary table and imported the newly generated CSV data
• Imported the data to the actual table using an inner join

A sample data

"id", "label"
1,"Name"
2,"Age"
3,"Gender"
4,"Date of birth"

I assigned the data to a variable

var data = `"id", "label"
1, "Name"
2,"Age"
3,"Gender"
4,"Date of birth"`

Initially, I achieved the result using a nested for loop. Considering performance, I eventually refactored the code to use only one for loop

The JavaScript code to generate the content of the new column

const dataAsArray = data.split('\n');

for(let datum of dataAsArray) {
  let currentRow = datum.split(',');
  let newColumn = currentRow[1].trim().toLowerCase().split(' ').join('_');
  currentRow += `,${newColumn}`;

  console.log(currentRow);   
}

The code above generated the data I needed. Which is, the content of the second column without spaces and separated by an underscore. I saved the result in a CSV file.

Then, I created a temporary table with the columns "id","label","code" and loaded the data using the command

LOAD DATA LOCAL INFILE '/Users/Chiamaka/Desktop/query_result.csv'
INTO TABLE `testtable` 
CHARACTER SET 'utf8' FIELDS ESCAPED BY '\\' TERMINATED BY ',' ENCLOSED BY '"' LINES 
TERMINATED BY '\n'
IGNORE 1 LINES

To update the actual table with the newly generated data using backticks to ensure that reserved names are accepted as strings.

UPDATE testtable tt
inner join actualtable actb on (tt.id = actb.id)
set actb.code = tt.`code`
where tt.id = actb.id;

This yielded the desired result

"id","label","code"
1,"Name","name"
2,"Age","age"
3,"Gender","gender"
4,"Date of birth","date_of_birth"