MySQL UPDATE 一个表，其中包含来自另一个表的多行，以使用 REGEXP 字边界更改拼写错误的单词

Question

我想用 misspelled_words_table 中的拼写更正更新 job_titles_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中‘Department’和‘Supervisor’没有变化，只有‘Animation’有变化。

** 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

UPDATE 并未累积应用更改。每个更改都应用于原始 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;

遍历 SELECT 的结果并将每个 UPDATE 作为动态 SQL 语句执行。

P.S.:您正在使用 RIGHT JOIN，而您应该使用 INNER JOIN。在这种情况下它没有坏处，但您可能需要阅读有关联接类型的信息，以便使用正确的类型。