❤️sql中delete不能删除select的结果集

小明的学习圈子2023-09-25数据库总结

delete语句是用来删除数据库中的数据的,而select查询语句是用来查询数据的。delete语句删除数据的操作是基于表的,而不是基于查询的结果。因此,无法直接使用delete语句删除select查询的结果。要删除select查询结果中的数据,你可以将查询的结果保存到一个临时表中,然后再使用delete语句删除临时表中的数据。或者使用其他方式来处理查询结果,例如使用其他编程语言或工具来操纵数据库。

原始的写法,报错如下

DELETE 
FROM
	personnel_filing 
WHERE
	certificateNo IN (
	SELECT
		certificateNo 
	FROM
		personnel_filing 
	WHERE
		certificateNo IN ( SELECT certificateNo FROM personnel_filing GROUP BY certificateNo HAVING count( certificateNo ) > 1 ORDER BY certificateNo ) 
	AND certificateType IS NULL 
	)

image-20230925205609481

修改后

DELETE 
FROM
	personnel_filing 
WHERE
	certificateNo IN (
	SELECT
		* 
	FROM
		(
		SELECT
			certificateNo 
		FROM
			personnel_filing 
		WHERE
			certificateNo IN ( SELECT certificateNo FROM personnel_filing GROUP BY certificateNo HAVING count( certificateNo ) > 1 ORDER BY certificateNo ) 
			AND certificateType IS NULL 
		) AS temp 
	)
Last Updated 2024/4/6 10:47:15