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

---

title: sql中delete不能删除select的结果集

date: 2023-09-25
categories:

• 数据库
  tags:
• 数据库
• 总结

---

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 
	)

修改后

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 
	)

AND certificateType IS NULL 
	) AS temp 
)

<Valine></Valine>