Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >=

What it means

A subquery in parentheses can stand for one value: after a comparison (WHERE shopper_id = (SELECT …)), as a column in the select list, in SET col = (SELECT …) or DECLARE @x int = (SELECT …). SQL Server checks while running that it returns at most one row. When one returns two or more, it stops the statement with error 512. (No rows is fine: the value is NULL.)

Because the check happens at run time, a query can work for months and fail the day the data gains a second matching row: a second order, a duplicate name, a customer with two addresses.

Common causes

1. A lookup that isn’t unique: matching on a name, an email without a unique constraint, or a LIKE pattern.
2. A correlated subquery in the select list that finds several child rows, such as one order total per shopper when a shopper has several orders.
3. A trigger written for one row. DECLARE @id int = (SELECT id FROM inserted) works for a single-row INSERT and fails as soon as a statement inserts, updates or deletes several rows. The message then names the trigger: Procedure trg_purchases_audit, Line 4.
4. UPDATE … SET col = (SELECT …) where the subquery isn’t tied to a single row of the source.
5. Duplicate rows in data that should be unique, from an import or a missing constraint.

How to fix it

Compare against the set with IN or EXISTS

If any of the matching rows will do, test membership instead of equality:

SELECT id, total
FROM dbo.purchases
WHERE shopper_id IN (SELECT id FROM dbo.shoppers WHERE name LIKE N'%a%');

EXISTS (SELECT 1 FROM … WHERE …) does the same for a correlated test. For a comparison against all of them, use > ALL (…) or an aggregate (> (SELECT MAX(…) …)).

Pick one row on purpose

Say which row you want with TOP (1) and an ORDER BY:

SELECT s.name,
       (SELECT TOP (1) p.total
        FROM dbo.purchases AS p
        WHERE p.shopper_id = s.id
        ORDER BY p.placed_at DESC) AS last_total
FROM dbo.shoppers AS s;

Without the ORDER BY, TOP (1) returns an arbitrary row, which hides the problem rather than solving it. An aggregate (SUM, MAX, COUNT) is the other way to turn many rows into one value.

Find where the extra rows come from

Group by the subquery’s matching column and look for counts above 1:

SELECT shopper_id, COUNT(*) AS matches
FROM dbo.purchases
GROUP BY shopper_id
HAVING COUNT(*) > 1;

If those rows shouldn’t exist, remove the duplicates and add a unique constraint so they can’t come back.

Write triggers for many rows

inserted and deleted hold every row the statement touched. Work with them as tables:

CREATE OR ALTER TRIGGER dbo.trg_purchases_audit ON dbo.purchases AFTER INSERT AS
BEGIN
  SET NOCOUNT ON;
  INSERT INTO dbo.purchase_audit (purchase_id, noted_at)
  SELECT id, SYSDATETIME() FROM inserted;
END;

Join instead of a subquery in UPDATE

UPDATE … FROM with a join makes the relationship explicit; aggregate in a derived table first if a target row has several matches:

UPDATE s
SET s.total_spent = t.spent
FROM dbo.shoppers AS s
JOIN (SELECT shopper_id, SUM(total) AS spent FROM dbo.purchases GROUP BY shopper_id) AS t
  ON t.shopper_id = s.id;

Assigning with SELECT @x = … doesn’t fail

SELECT @last = placed_at FROM … WHERE shopper_id = 1 doesn’t raise 512 when several rows match: it assigns one of the values and carries on. That’s why replacing SET @x = (SELECT …) with it makes the error go away while leaving the bug. Use TOP (1) with ORDER BY, or an aggregate.

Reproduce it

On SQL Server 2022 (16.0.4295.3) with sqlcmd, shoppers Ada (1) and Grace (2), and three purchases, two of them Ada’s:

SELECT id, total FROM seo_sqlerr_data.purchases
WHERE shopper_id = (SELECT id FROM seo_sqlerr_data.shoppers WHERE name LIKE N'%a%');

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

The correlated subquery in the select list and DECLARE @last datetime2(0) = (SELECT placed_at … WHERE shopper_id = 1) failed the same way; SELECT @last = placed_at … set @last to one of Ada’s two dates without an error. An UPDATE … SET name = (SELECT … WHERE shopper_id = 1) failed too, ending with The statement has been terminated. The IN and TOP (1) versions returned rows.

A trigger declared DECLARE @id int = (SELECT id FROM inserted) handled a one-row insert, then:

INSERT INTO seo_sqlerr_data.purchases VALUES
  (5, 2, 9.00, '2026-10-05 10:00'), (6, 1, 3.00, '2026-10-05 11:00');

Msg 512, Level 16, State 1, Server 7732422b7f56, Procedure trg_purchases_audit, Line 4
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

Neither row was inserted. With the trigger rewritten to INSERT … SELECT … FROM inserted, the same statement inserted both rows and audited both. SQL Server 2019 and 2025 printed the same message.

In Inlet

When SQL Server refuses, Inlet shows its message with SQL Server error 512, state 1, severity 16 and links to this page. A table’s definition includes its triggers, so you can read the trigger named in the error. With your own Anthropic API key, Ask Claude (⌘L) can rewrite the subquery or the trigger; it sends the schema and the SQL, never rows.