I’ll want to add a new field to a table and I have the data in a flat file that I’ve been working on.
For example, we recently broke words into sounds and needed to add them back to the database.
The words look like this in the BBEdit file:
a • l o t
‘a • c re
ai m s
The easies way that I’ve found to bring the sounds into the larger table is to first create a new table with id and sounds as the fields, import the sounds data, and then merge the two tables. Note: This is how it works with PHP MyAdmin. It may be slightly different from the command line.
This code imports the data into the Sounds table. Note that it ends with a semi-colon and no comma.
INSERT INTO `Sounds` (`id`, `sounds`) VALUES
(1, 'a • l o t'),
(2, 'a • c re'),
(3, 'a f • t er'),
(4, 'ai m s'),
...
(478, 'c o m • p o s t');
Now I can merge the sounds into another table in the same database by using the UPDATE command. You must include both tables after UPDATE, even though you are only updating data in one of the tables. Note that when using PHP MyAdmin you need to indicate the fields with the table name in backquotes, a dot, and then the field in backquotes. This may be different in the command line.
UPDATE `Pictures`, `Sounds`
SET `Pictures`.`sounds`=`Sounds`.`sounds`
WHERE `Pictures`.`id` = `Sounds`.`id`;
Here’s another example.
UPDATE `Pictures`, `ArticIV_Phonemes`
SET `Pictures`.`concatenated_phonemes` = `ArticIV_Phonemes`.`Concatenated_phonemes`
WHERE `Pictures`.`id` = `ArticIV_Phonemes`.`id`