On this page
Why it happens
Error 512 means a subquery was placed somewhere that expects a single value, but at run time it returned more than one row. SQL Server cannot compare one column against many values with =, so it stops the statement instead of guessing which row you meant.
The message lists the operators that force a subquery to be scalar: =, !=, <, <=, >, >=, plus any place the subquery is used as an expression such as a column in the SELECT list or the right side of a SET assignment. If the inner query can ever match two or more rows, the error is possible.
Common causes are a lookup that is not unique, a correlated subquery whose join is not selective enough to pin down one row per outer row, or an UPDATE ... SET col = (SELECT ...) whose inner query returns many rows. It is severity 16, a normal statement level error you can fix in the query.
Examples
Scalar subquery after = in a WHERE clause
-- Several orders belong to city 'London', so the subquery returns many rows.
SELECT o.OrderId, o.Total
FROM dbo.Orders AS o
WHERE o.CustomerId = (
SELECT c.CustomerId
FROM dbo.Customers AS c
WHERE c.City = 'London'
);Msg 512, Level 16, State 1, Line 3 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Scalar subquery in the SELECT list
-- A customer can have more than one order, so this expression is not scalar.
SELECT
c.CustomerId,
c.Name,
(SELECT o.Total FROM dbo.Orders AS o WHERE o.CustomerId = c.CustomerId) AS OrderTotal
FROM dbo.Customers AS c;Msg 512, Level 16, State 1, Line 5 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
How to fix it
Use IN instead of = when several matches are valid
If it is correct for the subquery to return many values, replace = with IN. The outer row then matches when its value is in the returned set, and multiple rows are allowed.
SELECT o.OrderId, o.Total
FROM dbo.Orders AS o
WHERE o.CustomerId IN (
SELECT c.CustomerId
FROM dbo.Customers AS c
WHERE c.City = 'London'
);Force a single row with an aggregate or TOP 1
When you genuinely want one value, make the subquery return exactly one row. An aggregate such as MAX or MIN collapses the rows, or use TOP 1 with a deterministic ORDER BY to pick a specific one.
-- Latest order total for the customer
SELECT
c.CustomerId,
c.Name,
(SELECT TOP 1 o.Total
FROM dbo.Orders AS o
WHERE o.CustomerId = c.CustomerId
ORDER BY o.OrderDate DESC) AS LatestTotal
FROM dbo.Customers AS c;Correlate the subquery so it matches one row
Often the subquery is missing a condition that ties it to the outer row. Add the correlation (and any key) so it returns exactly one row per outer row.
SELECT o.OrderId, o.Total
FROM dbo.Orders AS o
WHERE o.CustomerId = (
SELECT c.CustomerId
FROM dbo.Customers AS c
WHERE c.City = 'London'
AND c.Email = o.CustomerEmail -- pins the lookup to one customer
);Rewrite the scalar subquery as a JOIN
A JOIN is usually clearer and faster than a scalar subquery, and it naturally handles the many-row case without error 512. Move the inner table into the FROM clause.
SELECT o.OrderId, o.Total
FROM dbo.Orders AS o
JOIN dbo.Customers AS c
ON c.CustomerId = o.CustomerId
WHERE c.City = 'London';Use UPDATE ... FROM for updates that read another table
An UPDATE ... SET col = (SELECT ...) fails with 512 when the inner query returns many rows. Rewrite it as an UPDATE ... FROM join so each target row is matched to a single source row.
UPDATE o
SET o.CustomerName = c.Name
FROM dbo.Orders AS o
JOIN dbo.Customers AS c
ON c.CustomerId = o.CustomerId;How to prevent it
Make sure any lookup used in a scalar position is unique. If a column is meant to identify one row, enforce it with a primary key or unique constraint so the subquery can never return two values.
Prefer JOINs over scalar subqueries for combining tables, and test each subquery on its own first: run it alone and confirm it returns a single row before dropping it into a comparison, SELECT list, or SET clause.
Common questions
When should I use IN instead of = with a subquery?
Use = only when the subquery is guaranteed to return exactly one row, such as a lookup on a unique key. Use IN when the subquery can legitimately return several values and you want the outer row to match any of them. Switching = to IN is the standard fix for error 512 when multiple matches are expected.
Why is a JOIN often better than a scalar subquery?
A JOIN handles the many-row case naturally, so it never raises error 512, and the optimizer can usually process it more efficiently than a correlated scalar subquery that runs once per outer row. A JOIN also makes the relationship between the tables explicit, which is easier to read and maintain.