// SQL Server error reference

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
Error message
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

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

  1. If any matching row will do, use IN or EXISTS instead of =.

    SELECT * FROM dbo.Orders
    WHERE CustomerId IN (SELECT CustomerId FROM dbo.Customers WHERE Country = 'NL');
  2. If you need exactly one value, say which with TOP (1) and ORDER BY, or an aggregate.

    SELECT @lastOrder = (SELECT TOP (1) OrderId FROM dbo.Orders WHERE CustomerId = @c ORDER BY OrderDate DESC);
  3. Rewrite triggers to work on the whole inserted set with a join.

    UPDATE c SET c.LastOrderAt = i.OrderDate
    FROM dbo.Customers AS c
    JOIN inserted AS i ON i.CustomerId = c.CustomerId;
  4. 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

More data conversion and truncation errors

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.

// fewer errors, faster fixes

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.