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

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;

并出现错误

错误代码：1054
“on 子句”中存在未知列“appraisal_lines.perceived_state”

我根据帖子编写了更新语句： MySQL 使用具有多个返回行的子查询更新字段值

Answer 1

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

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;

现在，在您的脚本中，您要将名为 appraisal_lines_subscribed 的表加入到名为 increments 的（派生）表中。连接条件引用了 increments 中的列和...等一下，appraisal_lines？那是什么？参与连接的表中没有同名的表。

我的猜测是，加入条件中的 appraisal_lines.perceived_state 引用应替换为 appraisal_lines_subscribed.current_perceived_state。

我的另一个猜测是，您在 ORDER BY 之后错过了 LIMIT 1，这是基于正在更新的列的名称（first _perceived_state）

Answer 2

解析器无法看到表 appraisal_lines，因为您正在更新 appraisal_lines_subscribed。

您在内部 SELECT 中指定 appraisal_lines 的唯一位置，别名为 increments

您还可以删除 ORDER BY code> 在内部 SELECT 中，无论内部数据的顺序如何，都会执行连接。