more than one row returned by a subquery used as an expression

最新推荐文章于 2021-12-18 11:26:37 发布

原创最新推荐文章于 2021-12-18 11:26:37 发布 · 1w 阅读

0 ·

CC 4.0 BY-SA版权

数据库错误经验专栏收录该内容

2 篇文章

订阅专栏

本文探讨了在SQL中使用IN子句进行批量删除操作时的常见错误及其优化方案。通过对比错误示例与两种正确方法，阐述了如何有效避免因查询结果集导致的问题。

有问题的sql

	delete from user where class_id in(
		(select class_id from class where class_grade = '高三'),
		(select class_id from class where class_grade = '高二')
	);

问题所在：

select class_id from class where class_grade = '高三';
查询出来的结果是多条,
select class_id from class where class_grade = '高二';
查询出来的结果也是多条。

这种写法不正确。至于为什么不能这么写,以后有时间再做研究。

正确的sql
方式一：

	delete from user where class_id in(
		select class_id 
		from class 
		where class_grade in('高三','高二')
	);

方式二：

	delete from user where class_id in(
		select class_id from class where class_grade = '高三'
	);

	delete from user where class_id in(
		select class_id from class where class_grade = '高二'
	);