mysql delete in_Mysql delete from where id in(select ...) 只能够删除一条数据

不是的，MySQL中的DELETE语句可以删除符合条件的多条数据。例如，可以使用以下语法删除表中ID为1、2、3的多条数据：

DELETE FROM 表名 WHERE ID IN (1,2,3);

也可以使用子查询来删除符合条件的多条数据，例如：

DELETE FROM 表名 WHERE ID IN (SELECT ID FROM 表名 WHERE 条件);

这样就可以删除符合条件的多条数据了。

相关问题

sql DELETE FROM t_project_disclose WHERE id IN ( SELECT tpd.id FROM t_project_disclose tpd WHERE tpd.project_plan_id NOT IN ( SELECT opp.ID FROM t_project_disclose tpd JOIN overhaul_project_plan opp ON tpd.project_plan_id = opp.ID ) AND tpd.version_id = 'd2576fc1e2be4258ac8b624b926cc44c'); 报以下错误 You can't specify target table 't_project_disclose' for update in FROM clause

这个错误是因为 MySQL 不支持在一个子查询中更新和查询同一个表。

为了解决这个问题，可以使用临时表或者多层嵌套查询来代替子查询。以下是使用多层嵌套查询的示例 SQL 语句：

DELETE FROM t_project_disclose WHERE id IN (
    SELECT id FROM (
        SELECT tpd.id FROM t_project_disclose tpd 
        WHERE tpd.project_plan_id NOT IN (
            SELECT opp.ID FROM t_project_disclose tpd 
            JOIN overhaul_project_plan opp ON tpd.project_plan_id = opp.ID
        ) 
        AND tpd.version_id = 'd2576fc1e2be4258ac8b624b926cc44c'
    ) AS tmp
);

在这个 SQL 语句中，首先使用了一个 SELECT 语句来获取需要删除的数据的 id，然后将其放入一个临时表 tmp 中，最后使用 DELETE 语句将临时表中的数据从表 `t_project_disclose` 中删除。

请注意，如果数据量很大，使用多层嵌套查询可能会导致性能问题，此时可以考虑使用临时表来解决。

DELIMITER $$ CREATE TRIGGER 学生删除 AFTER DELETE ON tb_student FOR EACH ROW BEGIN DELETE FROM Score WHERE tb_score.Student_Id IN (SELECT Student_Id FROM deleted); WHERE tb_score.Student_Id IN (SELECT Student_Id FROM deleted); END $$报错错误代码： 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE tb_score.Student_Id IN (SELECT Student_Id FROM deleted); END' at line 6

在你的 SQL 语句中，有两个 WHERE 关键字，导致语法出错。你需要删除其中一个。请尝试使用以下代码：

DELIMITER $$ 
CREATE TRIGGER 学生删除 AFTER DELETE ON tb_student 
FOR EACH ROW 
BEGIN 
    DELETE FROM Score WHERE tb_score.Student_Id IN (SELECT Student_Id FROM deleted); 
END $$ 
DELIMITER ;

注意，这里我添加了 `DELIMITER` 关键字，它可以帮助你正确定义和结束你的触发器。