mysql - 通过在同一表中选择具有另一个条件的行来更新具有 where 条件的行

Question

我遇到了一个特定的查询，这很棘手，但我无法解决它。

这里我有一个包含许多行及其列的表格问题(id、subject_id、topic_id、description、pic、cha、pica、chb、picb、chc、picc、chd、picd、answer、attempt、correct、level、检查，BookId，页码)

现在我想更新 topic_id 为 lm 的表问题，要插入的值来自从 topic_id 为 newton 的同一个表问题中获取值

我尝试了 3 个查询，但都不起作用:

1.>

UPDATE questions
SET
id = newdata.id,
subject_id = newdata.subject_id,
topic_id = newdata.topic_id,
description = newdata.description,
pic = newdata.pic,
cha = newdata.cha,
pica = newdata.pica,
chb = newdata.chb,
picb = newdata.picb,
chc = newdata.chc,
picc = newdata.picc,
chd = newdata.chd,
picd = newdata.picd,
answer = newdata.answer,
attempt = newdata.attempt,
correct = newdata.correct,
level = newdata.level,
checked = newdata.checked,
BookId = newdata.BookId,
PageNumber = newdata.PageNumber
FROM
(SELECT * FROM questions WHERE topic_id = 'newton') newdata
WHERE
topic_id = 'lm'
AND topic_id = newdata.topic_id;

2.>

UPDATE questions
SET
id = newdata.id,
subject_id = newdata.subject_id,
topic_id = newdata.topic_id,
description = newdata.description,
pic = newdata.pic,
cha = newdata.cha,
pica = newdata.pica,
chb = newdata.chb,
picb = newdata.picb,
chc = newdata.chc,
picc = newdata.picc,
chd = newdata.chd,
picd = newdata.picd,
answer = newdata.answer,
attempt = newdata.attempt,
correct = newdata.correct,
level = newdata.level,
checked = newdata.checked,
BookId = newdata.BookId,
PageNumber = newdata.PageNumber
from questions newdata
WHERE
questions.topic_id = 'lm'
AND newdata.topic_id = 'newton';

3.>

UPDATE questions
SET
id = (SELECT id FROM questions WHERE topid_id = 'newton'),
subject_id = (SELECT subject_id FROM questions WHERE topid_id = 'newton'),
topic_id = (SELECT topic_id FROM questions WHERE topid_id = 'newton'),
description = (SELECT description FROM questions WHERE topid_id = 'newton'),
pic = (SELECT pic FROM questions WHERE topid_id = 'newton'),
cha = (SELECT cha FROM questions WHERE topid_id = 'newton'),
pica = (SELECT pica FROM questions WHERE topid_id = 'newton'),
chb = (SELECT chb FROM questions WHERE topid_id = 'newton'),
picb = (SELECT picb FROM questions WHERE topid_id = 'newton'),
chc = (SELECT chc FROM questions WHERE topid_id = 'newton'),
picc = (SELECT picc FROM questions WHERE topid_id = 'newton'),
chd = (SELECT chd FROM questions WHERE topid_id = 'newton'),
picd = (SELECT picd FROM questions WHERE topid_id = 'newton'),
answer = (SELECT answer FROM questions WHERE topid_id = 'newton'),
attempt = (SELECT attempt FROM questions WHERE topid_id = 'newton'),
correct = (SELECT correct FROM questions WHERE topid_id = 'newton'),
level = (SELECT level FROM questions WHERE topid_id = 'newton'),
checked = (SELECT checked FROM questions WHERE topid_id = 'newton'),
BookId = (SELECT BookId FROM questions WHERE topid_id = 'newton'),
PageNumber = (SELECT PageNumber FROM questions WHERE topid_id = 'newton')
WHERE topic_id = 'lm';

这些都不起作用。

请帮帮我。

如果我犯了任何简单的错误，我深表歉意。

Answer 1

在 MySQL 中，您不能在更新中引用同一个表，除非您使用 join 或过度嵌套的子查询。不过，如果您对 MySQL 使用正确的语法，您的查询可能会起作用。第一个是:

UPDATE questions q JOIN
       (SELECT * FROM questions WHERE topic_id = 'newton') newdata
       ON q.topic_id = newdata.topic_id
    SET q.id = newdata.id,
        q.subject_id = newdata.subject_id,
        q.topic_id = newdata.topic_id,
        q.description = newdata.description,
        q.pic = newdata.pic,
        q.cha = newdata.cha,
        q.pica = newdata.pica,
        q.chb = newdata.chb,
        q.picb = newdata.picb,
        q.chc = newdata.chc,
        q.picc = newdata.picc,
        q.chd = newdata.chd,
        q.picd = newdata.picd,
        q.answer = newdata.answer,
        q.attempt = newdata.attempt,
        q.correct = newdata.correct,
        q.level = newdata.level,
        q.checked = newdata.checked,
        q.BookId = newdata.BookId,
        q.PageNumber = newdata.PageNumber
WHERE q.topic_id = 'lm';