Error 512

Severity

Message text

Subquery returned more than 1 value.  This is illegal when the subquery follows =, !=, <, <= , >, >=, or when the  subquery is used as an expression.

Explanation

When an expression subquery returns more than one result, it violates the relational operator rule for the outer query, and error 512 occurs. An example of an expression subquery that returns one result follows:

1> select * from table_one where x =
2> (select sum(a) from table_two
3> where b = table_one.y)
4> go

An example of a query that returns more than one result and causes error 512 follows:

1> use pubs2
2> go

1> select authors.au_id from authors where
2> authors.au_id = (select titleauthor.au_id
3> from titleauthor)
4> go

Msg 512, Level 16, State 1:
Line 1:
Subquery returned more than 1 value.  This is illegal
when the subquery follows =, !=, <, <= , >, >=, or when 
the subquery is used as an expression.

Action

To correct the problem in the example, use “in” in place of “=”, as in the following example:

1> select authors.au_id from authors where
2> authors.au_id in (select titleauthor.au_id 
3> from titleauthor)
4> go

au_id 
----------- 
172-32-1176 
213-46-8915 
  .
  .
899-46-2035 
998-72-3567 

(19 rows affected)

Additional information

Refer to the chapter “Subqueries: Using Queries Within Other Queries” in the Transact-SQL User’s Guide.

Versions in which this error is raised

All versions