


Why Does My SQL Subquery Produce an 'Only One Expression Allowed' Error?
Jan 11, 2025 pm 01:17 PMSQL Subquery Error: "Only One Expression Allowed"
This error, "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS," arises when a subquery used within an IN
clause returns multiple columns. The IN
operator expects a single-column result set for comparison.
Problematic Query:
The following query attempts to use a subquery returning A_ID
and a count (ud
) within an IN
clause:
select count(distinct dNum) from myDB.dbo.AQ where A_ID in (SELECT DISTINCT TOP (0.1) PERCENT A_ID, COUNT(DISTINCT dNum) AS ud FROM myDB.dbo.AQ WHERE M > 1 and B = 0 GROUP BY A_ID ORDER BY ud DESC)
Root Cause:
The inner SELECT
statement returns two columns: A_ID
and ud
. The IN
operator cannot handle this; it requires a single column for comparison against the A_ID
column in the outer query.
Corrected Query:
The solution is to modify the subquery to return only the A_ID
column. The sorting by count can be done within the subquery using ORDER BY COUNT(DISTINCT dNum) DESC
:
select count(distinct dNum) from myDB.dbo.AQ where A_ID in (SELECT DISTINCT TOP (0.1) PERCENT A_ID FROM myDB.dbo.AQ WHERE M > 1 and B = 0 GROUP BY A_ID ORDER BY COUNT(DISTINCT dNum) DESC)
This revised query correctly selects the top 10% of A_ID
values based on the count of distinct dNum
values and then uses those A_ID
values to filter the outer query.
The above is the detailed content of Why Does My SQL Subquery Produce an 'Only One Expression Allowed' Error?. For more information, please follow other related articles on the PHP Chinese website!

Hot Article

Hot tools Tags

Hot Article

Hot Article Tags

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

Reduce the use of MySQL memory in Docker

How do you alter a table in MySQL using the ALTER TABLE statement?

How to solve the problem of mysql cannot open shared library

What is SQLite? Comprehensive overview

Run MySQl in Linux (with/without podman container with phpmyadmin)

How do I secure MySQL against common vulnerabilities (SQL injection, brute-force attacks)?

Running multiple MySQL versions on MacOS: A step-by-step guide

How do I configure SSL/TLS encryption for MySQL connections?
