Just some miscellaneous code that I\u2019ve been using to update fields in my database. Basically lots of trimming and concatenating. A little substring manipulating thrown in for good measure.<\/p>\n
\nSELECT * FROM `words_for_slps` WHERE `F` REGEXP 'z$' AND `pronunciation` REGEXP 'z$'\n\nUPDATE`words_for_slps` SET `phonemes` = TRIM(TRAILING 's' FROM phonemes) WHERE `F` REGEXP 'z$' AND `phonemes` REGEXP 's$' AND `word` = 'Ares'\n\nUPDATE`words_for_slps` SET `phonemes` = CONCAT(phonemes,'z') WHERE `F` REGEXP 'z$' AND `pronunciation` REGEXP 'z$'\n\nUPDATE`words_for_slps` SET `phonemes` = TRIM(TRAILING 's hz' FROM phonemes) WHERE `F` REGEXP 'hz$' AND `phonemes` REGEXP 'hz$'\n\nUPDATE`words_for_slps` SET `phonemes` = CONCAT(phonemes,'\u0283 \u025b z') WHERE `F` REGEXP 'z$'\n\nUPDATE`words_for_slps` SET `phonemes` = CONCAT(phonemes,'z') WHERE `F` REGEXP 'z$' AND `pronunciation` REGEXP 'z$'\nUPDATE`words_for_slps` SET `phonemes` = CONCAT(phonemes,' \u025b s') WHERE `F` REGEXP 's' AND `phonemes` REGEXP 'n$'\n\nUPDATE `words_for_slps` SET `phonemes` = TRIM(LEADING 'c' FROM phonemes) WHERE `word` REGEXP '^c' AND `I` LIKE 'c'\nUPDATE `words_for_slps` SET `phonemes` = CONCAT('k',phonemes) WHERE `phonemes` REGEXP '^ ' AND `I` LIKE 'c'\n\nUPDATE `words_for_slps` SET `phonemes` = TRIM(LEADING 'k' FROM phonemes) WHERE `word` REGEXP '^cy' AND `phonemes` REGEXP '^k'\nUPDATE `words_for_slps` SET `phonemes` = CONCAT('s',phonemes) WHERE `word` REGEXP '^cy' AND `phonemes` REGEXP '^ '\n\nUPDATE `words_for_slps` SET `phonemes` = REPLACE(phonemes, 't \u025b,i d', 't \u0259 d') WHERE `phonemes` REGEXP 't \u025b,i d$' AND `pronunciation` REGEXP 't u d$'\n\nUPDATE `words_for_slps` SET `phonemes` = REPLACE(phonemes, 'n z', 'nz') WHERE `pronunciation` REGEXP 'n z$' AND `phonemes`REGEXP 'n z$'\n<\/code><\/pre><\/p>\nI used these select statements to look for words that need some manual cleanup.
\n
\nSELECT * FROM `words_for_slps` WHERE `F` <> SUBSTRING_INDEX(phonemes, ' ', -1)\nSELECT * FROM `words_for_slps` WHERE `word` REGEXP '^w' AND `pronunciation` NOT REGEXP '^w' AND `pronunciation` IS NOT NULL\n<\/code><\/pre><\/p>\nThis works when I want to update the fields in one table with values from fields in another table.
\n
\nUPDATE words_for_slps, words_for_slpsBAK12\nSET words_for_slps.grade = words_for_slpsBAK12.grade\nWHERE words_for_slps.word_id = words_for_slpsBAK12.word_id;\n<\/code><\/pre><\/p>\nEven though I\u2019m only updating one table you\u2019d think this should work, but it doesn’t.
\n
\nUPDATE `words_for_slps` \nLEFT JOIN `words_for_slpsBAK12` ON `words_for_slps.word_id` = `words_for_slpsBAK12.word_id`\nSET `words_for_slps.grade` = `words_for_slpsBAK12.grade`\n<\/code><\/pre><\/p>\n","protected":false},"excerpt":{"rendered":"Just some miscellaneous code that I\u2019ve 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 … Continue reading Making MySQL do what you want.<\/span><\/a><\/p>\n","protected":false},"author":6,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[27],"tags":[],"class_list":["post-1263","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/posts\/1263","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1263"}],"version-history":[{"count":0,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/posts\/1263\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1263"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1263"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1263"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}