Making MySQL do what you want.

Just some miscellaneous code that I’ve been using to update fields in my database. Basically lots of trimming and concatenating. A little substring manipulating thrown in for good measure.

SELECT *  FROM `words_for_slps` WHERE `F` REGEXP 'z$' AND `pronunciation` REGEXP 'z$'

UPDATE`words_for_slps` SET `phonemes` = TRIM(TRAILING 's' FROM phonemes) WHERE `F` REGEXP 'z$' AND `phonemes` REGEXP 's$' AND `word` = 'Ares'

UPDATE`words_for_slps` SET `phonemes` = CONCAT(phonemes,'z') WHERE `F` REGEXP 'z$' AND `pronunciation` REGEXP 'z$'

UPDATE`words_for_slps` SET `phonemes` = TRIM(TRAILING 's hz' FROM phonemes) WHERE `F` REGEXP 'hz$' AND `phonemes` REGEXP 'hz$'

UPDATE`words_for_slps` SET `phonemes` = CONCAT(phonemes,'ʃ ɛ z') WHERE `F` REGEXP 'z$'

UPDATE`words_for_slps` SET `phonemes` = CONCAT(phonemes,'z') WHERE `F` REGEXP 'z$' AND `pronunciation` REGEXP 'z$'
UPDATE`words_for_slps` SET `phonemes` = CONCAT(phonemes,' ɛ s') WHERE `F` REGEXP 's' AND `phonemes` REGEXP 'n$'

UPDATE `words_for_slps` SET `phonemes` = TRIM(LEADING 'c' FROM phonemes) WHERE `word` REGEXP '^c' AND `I` LIKE 'c'
UPDATE `words_for_slps` SET `phonemes` = CONCAT('k',phonemes) WHERE `phonemes` REGEXP '^ ' AND `I` LIKE 'c'

UPDATE `words_for_slps` SET `phonemes` = TRIM(LEADING 'k' FROM phonemes) WHERE `word` REGEXP '^cy' AND `phonemes` REGEXP '^k'
UPDATE `words_for_slps` SET `phonemes` = CONCAT('s',phonemes) WHERE `word` REGEXP '^cy' AND `phonemes` REGEXP '^ '

UPDATE `words_for_slps` SET `phonemes` = REPLACE(phonemes, 't ɛ,i  d', 't ə d') WHERE `phonemes` REGEXP 't ɛ,i  d$' AND `pronunciation` REGEXP 't u d$'

UPDATE `words_for_slps` SET `phonemes` = REPLACE(phonemes, 'n z', 'nz') WHERE `pronunciation` REGEXP 'n z$' AND `phonemes`REGEXP 'n z$'

I used these select statements to look for words that need some manual cleanup.

SELECT *  FROM `words_for_slps` WHERE `F` <> SUBSTRING_INDEX(phonemes, ' ', -1)
SELECT *  FROM `words_for_slps` WHERE `word` REGEXP '^w' AND `pronunciation` NOT REGEXP '^w' AND `pronunciation` IS NOT NULL

This works when I want to update the fields in one table with values from fields in another table.

UPDATE words_for_slps, words_for_slpsBAK12
SET words_for_slps.grade = words_for_slpsBAK12.grade
WHERE words_for_slps.word_id = words_for_slpsBAK12.word_id;

Even though I’m only updating one table you’d think this should work, but it doesn’t.

UPDATE `words_for_slps` 
LEFT JOIN `words_for_slpsBAK12` ON `words_for_slps.word_id` = `words_for_slpsBAK12.word_id`
SET `words_for_slps.grade` = `words_for_slpsBAK12.grade`