SQL Server error 512: Subquery returned more than 1 value
A subquery used as a single value (after =, <, > or in SET) returned several rows. Make it return one row, or use IN, EXISTS or a join.
- Error number
512- Severity
- 16
- Category
- Data conversion and truncation
What it means
A subquery in a place that expects one value, such as WHERE x = (SELECT ...), SET @v = (SELECT ...) or a column in the select list, must return at most one row. If it returns two or more at run time, SQL Server raises 512. The query may work for months and then fail once the data contains a duplicate.
Common causes
- The subquery's filter isn't unique, for example looking up a customer by name when two customers share it.
- A trigger written for single-row changes that does
SELECT @id = (SELECT id FROM inserted), failing on multi-row inserts or updates. - Duplicate rows appearing in a lookup table that was assumed to be unique.
How to fix it
-
If any matching row will do, use
INorEXISTSinstead of=.SELECT * FROM dbo.Orders WHERE CustomerId IN (SELECT CustomerId FROM dbo.Customers WHERE Country = 'NL');
-
If you need exactly one value, say which with
TOP (1)andORDER BY, or an aggregate.SELECT @lastOrder = (SELECT TOP (1) OrderId FROM dbo.Orders WHERE CustomerId = @c ORDER BY OrderDate DESC);
-
Rewrite triggers to work on the whole
insertedset with a join.UPDATE c SET c.LastOrderAt = i.OrderDate FROM dbo.Customers AS c JOIN inserted AS i ON i.CustomerId = c.CustomerId;
-
Find the duplicates that broke the assumption, and add a unique constraint if the column should be unique.
SELECT Name, COUNT(*) FROM dbo.Customers GROUP BY Name HAVING COUNT(*) > 1;
Reproduce it safely
SELECT (SELECT v FROM (VALUES (1), (2)) AS t(v)); -- Msg 512
Related errors
8120Column not in GROUP BY or aggregate21000PostgreSQL: more than one row returned by a subquery1242MySQL: Subquery returns more than 1 rowORA-01427Oracle: single-row subquery returns more than one row
More data conversion and truncation errors
8152String or binary data truncated2628Truncated in table, column245Conversion failed converting a value241Conversion failed for date and/or time242Out-of-range date conversion8115Arithmetic overflow error converting8114Error converting data type8134Divide by zero error encountered
The code and message text are from the Microsoft Database Engine events and errors reference. Browse the full SQL Server error list or search every database error.
Work on SQL Server in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for SQL Server and the other databases you use.