2不能指定目标表'p‘以便在FROM子句SQL2.sql 7 15中进行更新

Question

这是我的疑问：

UPDATE pupils p
SET p.rollIdentity = NULL
WHERE p.id IN (SELECT pup469.id FROM pupils pup469
inner JOIN pupils pup470 ON pup470.rollIdentity = pup469.rollIdentity
where pup469.school_id = 469 and pup469.year = 10
AND pup470.school_id = 470 AND pup470.year = 3)

因此，基本上，我只需要更新在另一所学校拥有相同rollIdentity的学生。我读到我应该用exist来代替，但是我不完全明白这一点，有人能进一步解释一下吗？谢谢

Answer 1

如果你把你的子查询埋得更深一点

drop table if exists t;
create table t(id int,school_id int, rollIdentity int, yr int);
insert into t values
(1,470, 10,3),
(2, 470, null,2),
(3, 469, 10,10),
(4,34,10,4);

UPDATE t p
SET p.rollIdentity = NULL
WHERE p.id IN 
(
select id from
(
SELECT pup469.id
  FROM t pup469
inner JOIN t pup470 ON pup470.rollIdentity = pup469.rollIdentity
where (pup469.school_id = 469 and pup469.yr = 10)
AND   (pup470.school_id = 470 AND pup470.yr = 3)
) s
);

select * from t;

你明白了

+------+-----------+--------------+------+
| id   | school_id | rollIdentity | yr   |
+------+-----------+--------------+------+
|    1 |       470 |           10 |    3 |
|    2 |       470 |         NULL |    2 |
|    3 |       469 |         NULL |   10 |
|    4 |        34 |           10 |    4 |
+------+-----------+--------------+------+
4 rows in set (0.00 sec)

但你为什么不想改变身份证呢？