SQL Server error 512: 'Subquery returned more than 1 value'

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. (error 512) means a subquery that you compared with =, or used as a single column value, found more than one row. Replace = with IN or EXISTS, or make the subquery return exactly one row.

Decide first which you meant. If several matches are valid, IN or EXISTS is the fix. If there should only ever be one, the data or the subquery's filter is wrong, and hiding it with TOP 1 would pick an arbitrary row.

The error

SELECT FullName
FROM dbo.Guests
WHERE Id = (SELECT GuestId FROM dbo.Bookings WHERE RoomNo = 204);

Msg 512, Level 16, State 1, Server MACHINE\LOCALDB#EAD42A01, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Why it happens

A subquery after = is a scalar subquery: SQL Server treats it as one value. Room 204 had two bookings, for Maria Garcia and David Chen, so the subquery produced two GuestId values and there was no single value to compare with. Unlike errors 4104 or 8120, this is a run-time error. The query compiles, and it only fails when the data gives more than one row, which is why it often appears weeks after the code shipped, on the first day two rows match.

The same message comes from a subquery used as a column. This query asks for one GuestId per booking, and the two bookings of room 204 broke it the same way:

SELECT RoomNo,
       (SELECT GuestId FROM dbo.Bookings AS b2 WHERE b2.RoomNo = b.RoomNo) AS GuestId
FROM dbo.Bookings AS b;

Msg 512, Level 16, State 1, Server MACHINE\LOCALDB#EAD42A01, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The fix

-- fix A: IN accepts any number of rows
SELECT FullName
FROM dbo.Guests
WHERE Id IN (SELECT GuestId FROM dbo.Bookings WHERE RoomNo = 204)
ORDER BY FullName;
GO
-- fix B: EXISTS, the same answer
SELECT g.FullName
FROM dbo.Guests AS g
WHERE EXISTS (SELECT 1 FROM dbo.Bookings AS b WHERE b.GuestId = g.Id AND b.RoomNo = 204)
ORDER BY g.FullName;

Both returned David Chen and Maria Garcia. IN reads most naturally when you compare one column; EXISTS is the general form and handles conditions on several columns. For a subquery used as a column, the fix is a JOIN that produces one output row per match, or an aggregate such as MAX or COUNT inside the subquery when one value per row is really what you want.

How it was reproduced

One script run with sqlcmd -S "(localdb)\MSSQLLocalDB" -d fix8_lab -I -i e512.sql on SQL Server 2022 LocalDB 16.0.1210.3 and sqlcmd 16.0.1000.6, in a throwaway database fix8_lab created for the test and dropped afterwards. A guests table, a bookings table with a foreign key, three fictional guests and three bookings, two of them for room 204.

Frequently asked

What does 'Subquery returned more than 1 value' mean?

SQL Server error 512 means a subquery used as a single value, after = or < or as a column in the select list, returned two or more rows when the statement ran.

Should I use IN or TOP 1 to fix error 512?

Use IN or EXISTS when several matching rows are valid. TOP 1 without ORDER BY returns an arbitrary row and hides a data problem, so use it only when any one match is acceptable.

Why did my query work yesterday and fail with error 512 today?

The error depends on the data. The query compiles fine and only fails on the day the subquery first finds two matching rows, for example a second booking for the same room.

More decoded errors in the Fixes category.