mysql - 使用子查询和连接表更新临时表失败

Question

我的数据库中有一个评估行表，想要从第一次评估中获取一些数据，并将其与最新评估中的相同数据一起显示，以便可以轻松地看到从第一次评估到当前评估的进展。我使用两次评估中的三个值以及当前评估中的其他数据来执行此操作。

为此，我认为我需要创建临时表来保存两组字段以及当前评估中的附加字段。

我的临时表创建如下:

CREATE TEMPORARY TABLE appraisal_lines_submitted (
    current_perceived_state INT(2),
    current_actual_state INT(2),
    current_desired_state INT(2),
    agreed_action_points TEXT,
    agreed_actions_target_date DATE,
    assessor_notes TEXT,
    category_id INT(2),
    sub_categories_id INT(2),
    sub_categories_description VARCHAR(45),
    sequence_number INT(3) PRIMARY KEY,
    first_perceived_state INT(2),
    first_actual_state INT(2),
    first_desired_state INT(2)
);

然后，我运行如下插入和选择语句，将当前评估中的数据放入临时表中:

INSERT INTO appraisal_lines_submitted (
    current_perceived_state,
    current_actual_state,
    current_desired_state,
    agreed_action_points,
    agreed_actions_target_date,
    assessor_notes,
    category_id,
    sub_categories_id,
    sub_categories_description,
    sequence_number)
SELECT appraisal_lines.perceived_state,
    appraisal_lines.actual_state,
    appraisal_lines.desired_state,
    appraisal_lines.agreed_action_points,
    DATE_FORMAT (appraisal_lines.agreed_actions_target_date, '%d/%m/%Y') AS agreed_actions_target_date_formatted,
    appraisal_lines.assessor_notes,
    appraisal_lines.assessment_category_id,
    assessment_sub_categories.id,
    assessment_sub_categories.description,
    assessment_sub_categories.sequence_number
FROM appraisal_lines LEFT JOIN assessment_sub_categories
    ON appraisal_lines.assessment_sub_category_id = assessment_sub_categories.id
WHERE hyperlink_token ='db678f8595edcd78d8ea7f055f7ee790b804c91e'
    AND assessment_category_id ='5'
ORDER BY sequence_number;

这工作正常。

但是，当我运行下面的更新语句时，它失败了

UPDATE appraisal_lines_submitted JOIN
    (SELECT appraisal_lines.perceived_state as update_perceived_state
     FROM appraisal_lines
     WHERE hyperlink_token ='d7cc7e1adc116e0dac31cbad34cd9a2b322c3507'
        AND assessment_category_id ='5'
     ORDER BY appraisal_lines.assessment_sub_category_id
    ) AS increments 
    ON increments.update_perceived_state = appraisal_lines.perceived_state
    SET appraisal_lines_submitted.first_perceived_state = update_perceived_state;

有错误

Error Code: 1054
Unknown column 'appraisal_lines.perceived_state' in 'on clause'

我根据帖子写了更新声明:MySQL Update a field value with subquery with multiple returning rows

Answer 1

这是您的脚本，其中一些位以粗体突出显示:

UPDATE <b>appraisal_lines_submitted</b> JOIN
    (SELECT appraisal_lines.perceived_state as update_perceived_state
     FROM appraisal_lines
     WHERE hyperlink_token ='d7cc7e1adc116e0dac31cbad34cd9a2b322c3507'
        AND assessment_category_id ='5'
     ORDER BY appraisal_lines.assessment_sub_category_id
    ) AS <b>increments</b> 
    ON <b>increments</b>.update_perceived_state = <b><i>appraisal_lines</i></b>.perceived_state
    SET appraisal_lines_submitted.first_perceived_state = update_perceived_state;

现在，在您的脚本中，您将加入一个名为 appraisal_lines_submitted 的表到名为 increments 的(派生)表。连接条件引用 increments 中的列以及来自...等一下的专栏，appraisal_lines ？那是什么？参与连接的表中没有同名的表。

我的猜测是，appraisal_lines.perceived_state连接条件中的引用应替换为 appraisal_lines_submitted.current_perceived_state .

我的另一个猜测是，你错过了 LIMIT 1之后ORDER BY ，它基于正在更新的列的名称 ( <b>first</b>_perceived_state )