mysql - 是否可以从 View 的基础表中删除行？

Question

我有一个包含 2 个表和 1 个 View 的数据库。其中一个表是查找表，我正在使用该 View 在一个表中呈现数据。

CREATE TABLE places (
 place_id INT NOT NULL AUTO_INCREMENT,
 place VARCHAR(20) NOT NULL,
 PRIMARY KEY (place_id)
);

CREATE TABLE tasks (
 task_id INT NOT NULL AUTO_INCREMENT,
 task VARCHAR(20) NOT NULL,
 place_id INT NOT NULL,
 PRIMARY KEY (task_id),
 FOREIGN KEY (place_id) REFERENCES places(place_id)
);

为了减少选择查询的大小，我创建了一个显示两个表信息的 View 。

CREATE VIEW view_planning AS
 SELECT tasks.task_id, tasks.task, places.place
 FROM tasks
 LEFT JOIN places ON places.place_id = tasks.place_id;

如果我想从任务表中删除一行，我必须执行以下操作:

DELETE FROM tasks WHERE 
 task_id = (SELECT task_id FROM tasks WHERE 
             task="some task" AND 
             place_id= (SELECT place_id FROM places WHERE place = "some place"));

我想使用这样的 View ，以便查询更短:

DELETE FROM tasks WHERE 
 task_id = (SELECT task_id FROM view_planning WHERE 
             task="some task" AND place= "some place");

但是我得到

Error Code: 1443. The definition of the table "view_planning" prevents operation DELETE on table "tasks".

有办法解决这个错误吗？

谢谢。

Answer 1

在 WHERE 子句中使用 JOIN 而不是子查询。

DELETE t FROM tasks AS t
JOIN view_planning AS vp ON t.task_id = vp.task_id
WHERE vp.task = "some_task" AND vp.place = "some_place";