mysql - 使用 "Subquery returns more than 1 row"时为 `SET`

Question

select tf.id from text_fields as tf WHERE tf.study_id NOT IN (select id from studies)

我正在使用它来查找孤立的记录，它工作正常。

但是，当我尝试使用 SET 将结果分配给 var 时，我收到“子查询返回超过 1 行”错误。

SET @text_field_ids := (select tf.id from text_fields as tf WHERE tf.study_id NOT IN (select id from studies))

对于上下文，我想使用 var 来记录 text_fields，例如

DELETE from text_fields WHERE id IN @text_field_ids

顺便说一下，我尝试将子查询直接传递给 DELETE，例如:

DELETE from text_fields WHERE id IN (select id from text_fields as tf WHERE tf.study_id NOT IN (select id from studies))

但这会产生错误 You can't specify target table 'text_fields' for update in FROM clause 因为显然你不能在 WHERE 子句中使用被删除的表。

Answer 1

你可以使用临时表:

create temporary table if not exists mytmptable select tf.id as id from text_fields as tf WHERE tf.study_id NOT IN (select id from studies)

然后你可以在删除中使用它:

DELETE from text_fields WHERE id IN (select Id from mytmptable)