您不能在 FROM 子句中为更新指定目标表 '' [英] You can't specify target table '' for update in FROM clause

问题描述

我正在尝试在 mysql 中运行此查询

I am trying to run this query in mysql

UPDATE studentassessment sa SET sa.issubmitted=FALSE WHERE 
sa.studentassessmentid <> 
    (SELECT studentassessmentid FROM studentassessment sa1 WHERE 
    sa.StudentId=sa1.StudentId
    AND sa.AssessmentPeriodId=sa1.AssessmentPeriodId 
    ORDER BY sa1.studentassessmentid DESC LIMIT 0, 1) 
AND sa.studentid=568;

这给了我这个错误

不能在 FROM 子句中指定目标表 'sa' 进行更新

You can't specify target table 'sa' for update in FROM clause

我发现了许多此类问题，但它们特定于他们被问到的查询，因此对我没有太大帮助，从 SO 的其中一个答案中，我尝试像这样更改查询

I found number of this type of questions but they are specific to the queries for which they are asked so didn't help me much, from one of the answers of SO I tried to change the query like this

UPDATE studentassessment sa SET issubmitted=FALSE WHERE 
sa.studentassessmentid <> 
    (
      SELECT studentassessmentid FROM 
        (
          SELECT studentassessmentid FROM studentassessment sa1 WHERE 
              sa.StudentId=sa1.StudentId
          AND sa.AssessmentPeriodId=sa1.AssessmentPeriodId 
          ORDER BY sa1.studentassessmentid DESC LIMIT 0, 1
         )  AS T
    )
AND sa.studentid=568;

然后我开始收到此错误

where 子句"中的未知列sa.StudentId"

Unknown column 'sa.StudentId' in 'where clause'

有什么帮助吗?

编辑

这也不起作用

UPDATE studentassessment sa
INNER JOIN 
(SELECT studentassessmentid FROM studentassessment sa1 WHERE 
    sa.StudentId=sa1.StudentId
    AND sa.AssessmentPeriodId=sa1.AssessmentPeriodId 
    ORDER BY sa1.studentassessmentid DESC LIMIT 0, 1) T
ON sa.studentassessmentid<>T.StudentAssessmentId 
SET sa.issubmitted=0
WHERE sa.studentid=568;

推荐答案

MySQL 有这个限制；您无法更新您从中选择的表格，并且您对此无能为力.

MySQL has that limitation; you can't update table which are you selecting from and you ain't doing nothing about it.

目前，您无法更新表并在子查询中从同一表中进行选择.

Currently, you cannot update a table and select from the same table in a subquery.

但是你可以这样做:

CREATE TEMPORARY TABLE studentassessmentids (
    `id` INT,
    PRIMARY KEY (`id`)
);

LOCK TABLE studentassessment WRITE;
INSERT INTO studentassessmentids (SELECT ... prepare your ids);

UPDATE studentassessment sa
SET sa.issubmitted=FALSE
WHERE sa.studentassessmentid NOT IN (
    SELECT id
    FROM studentassessmentids AS ids
    WHERE ids.id = sa.studentassessmentid
)

UNLOCK TABLES;
DROP TEMPORARY TABLE studentassessmentids;

我从头开始写了代码，所以它不能直接工作，但希望你能采纳这个想法.

I wrote the code from the top of my had, so it won't work directly, but hopefully you'll be able to adopt the idea.

如果您可以将查询重写为一组 JOIN 语句，例如(来自 mysql.com 的示例):

And if you could rewrite your query to group of JOIN statements, like (example from mysql.com):

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

它应该可以工作，但我不知道如何在不编写难以阅读的代码的情况下使用连接实现 NOT IN.

It should work, but I don't see how you can implement NOT IN with joins without writing hardly readable code.

这篇关于您不能在 FROM 子句中为更新指定目标表 ''的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！