SQL Server Error : 116, Severity: 15. Only one expression can be specified in

SQL Server Error : 116 Details

SQL Server Error: 116
Severity: 15
Event Logged or not: No
Description:
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Severity 15 Description:
Indicates syntax errors in the Transact-SQL command.

Example:

SELECT [ID], [Date], [Description], [Count] FROM [dbo].[Order] WHERE [ID] NOT IN (SELECT [ID], [NumberofStock] FROM [dbo].[Stock]) Msg 116, Level 16, State 1, Line 4 Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

This error usually occurs when a sub-query is used in conjunction with the IN or NOT IN logical operator. The IN logical operator checks to see if a given value matches any value in a subquery or a list. The NOT IN logical operator is used to reject a subquery or list.

 

Solution for Resolving the Error

1. One way to avoid this error is to follow the error message’s advice and use only one expression or column in the SELECT subquery statement.
2. Another way to avoid this error is to rewrite the SELECT statement, using the EXISTS operator or the INNER JOIN when the IN operator is used, and the NOT EXISTS operator or the LEFT OUTER JOIN when the NOT IN operator is used.

In the case of the IN operator, the SELECT statement can be used to return all ID records that have corresponding records for Stock in Stock Table.

Because only unique ID records are required in the output, the DISTINCT clause can be included. If there are multiple ID records for a given ID record, the ID be returned multiple times in the output which is the need for the use of the DISTINCT clause.

The EXISTS operator can also be used to rewrite the SELECT statement that returns ID records that have corresponding ID records for Stock in Stock Table.

In the case of the NOT IN operator to be used, the SELECT statement can be used to return all ID  records that do not have corresponding ID records for Stock in Stock Table

SQL Server Error Code and solution summary

SQL Server Error: 116
Severity: 15
Event Logged or not: No
Description:
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Make sure only one column is returned in “IN” Statement use and try rewrite query correctly.