MySQL使用另一个表中的多行更新一个表，以使用REGEXP单词边界更改拼写错误的单词

Question

我想用job_titles_table中的拼写更正来更新misspelled_words_table。

** job_titles_table **                               ** misspelled_words_table **
job_title_id    job_title                            job_title_id  misspelled_word   correct_spelling
15802           animation dept superviser            15802         animation         Animation
15803           animator / character key assistant   15802         dept              Department
15804           Lead 2D Animator                     15802         superviser        Supervisor
15805           Character Technical Director         15803         animator          Animator
15806           character layout apprentice          15803         assistant         Assistant
15807           Production Coordinator               15803         key               Key
15808           lighting and render engineer         15806         apprentice        Apprentice
                                                     15806         layout            Layout
                                                     15808         engineer          Engineer
                                                     15808         lighting          Lighting
                                                     15808         render            Render

此查询…

UPDATE job_titles_table j
RIGHT JOIN misspelled_words_table t USING (j.job_title_id)
SET j.job_title = REPLACE(j.job_title,t.misspelled_word,t.correct_spelling)

得出以下结果。请注意“部门”和“主管”在15802中没有更改，只有“动画”被更改。

** job_titles_table **
job_title_id    job_title
15802           Animation dept superviser
15803           Animator / character key assistant
15804           Lead 2D Animator
15805           Character Technical Director
15806           character layout Apprentice
15807           Production Coordinator
15808           lighting and render Engineer

Answer 1

更新没有累计地应用更改。每个更改都应用于原始的j.job_title字符串。请注意，在每一种情况下，确切的一个词已经固定，但哪个词是不可预测的。例如，在15806，“学徒”是固定的，而不是“布局”。

我想说的是，您将无法使用这种技术来完成单个联合UPDATE语句中的所有替换。

在标准SQL中这样做的最好方法是编写游标循环，但不幸的是，MySQL不支持UPDATE WHERE CURRENT OF CURSOR。

一个简单的解决方法是使用SELECT将表连接在一起，而SELECT的输出将是一个字符串，它是一个完整的UPDATE语句。

SELECT CONCAT( 
  'UPDATE job_titles_table SET job_title = REPLACE(job_title, ',
  QUOTE(t.misspelled_word), ', ',
  QUOTE(t.correct_spelling),
  ') WHERE job_title_id = ', t.job_title_id, ';'
) AS _sql
FROM misspelled_words_table t;

输出应该是一系列字符串，如：

UPDATE job_titles_table SET job_title = REPLACE(job_title, 'animation', 'Animation') WHERE job_title_id = 15802;
UPDATE job_titles_table SET job_title = REPLACE(job_title, 'department', 'Department') WHERE job_title_id = 15802;

循环选择和执行每个更新的结果，作为一个动态SQL语句。

P.S.：在应该使用RIGHT JOIN的地方，您使用的是INNER JOIN。在这种情况下，这并没有什么害处，但是您可能需要阅读有关联接类型的内容，以便使用正确的类型。

Answer 2

所以..。拼错的单词中的单引号开始引起麻烦..。这是新的查询..。SELECT CONCAT('UPDATE job_title SET job_title = REPLACE(job_title,''', REPLACE(s.misspelled_word,char(39),CONCAT(char(39),char(39))),''',''', REPLACE(s.correct_spelling,char(39),CONCAT(char(39),char(39))),''')', ' WHERE job_title_id = ', job_title_id,';') AS sql_update_statement FROM common_misspellings AS s JOIN job_title AS j WHERE j.job_title REGEXP CONCAT('[[:<:]]',REPLACE(s.misspelled_word,'.','[[.period.]]'),'[[:>:]]')