CODEWITHSUNDEEP
sql
LT268
LT268

Reputation: 135

Subquery returned more than 1 value. This is not permitted when the subquery follows>= or when the subquery is used as an expression

Subquery returned more than 1 value error. How to solve this?

SELECT top 1 address
FROM tblAdr A
WHERE A.pkey=
  (SELECT b.pkey FROM tblMachine b WHERE b.ADDRESS_PKEY IS NULL
  )

Upvotes: 1

Views: 67

Answers (4)

Gurwinder Singh
Gurwinder Singh

Reputation: 39487

Use IN instead of = as your subquery returns multiple matching rows.

SELECT top 1 address
FROM tblAdr A
WHERE A.pkey IN
  (SELECT b.pkey FROM tblMachine b WHERE b.ADDRESS_PKEY IS NULL
  );

Also, always use top queries with order by.

Upvotes: 0

Noman Akhtar
Noman Akhtar

Reputation: 730

This subquery "select b.pkey from tblMachine b where b.ADDRESS_PKEY is null" returns multiple value which you can't compare with "=" operator.but you want this try in clause

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521409

The subquery (the quantity in parentheses) is returning more than one pkey value, which isn't allowed because a single scalar value is required. If you are content with checking whether a pkey in tblAdr matches any of the values in the subquery, then you can use WHERE A.pkey IN (...) as follows:

SELECT TOP 1 address
FROM tblAdr A
WHERE A.pkey IN (SELECT b.pkey FROM tblMachine b WHERE b.ADDRESS_PKEY IS NULL)

Upvotes: 2

geofftnz
geofftnz

Reputation: 10092

Change the = to an IN

select top 1 address from tblAdr A where A.pkey in (select b.pkey from tblMachine b where b.ADDRESS_PKEY is null)

This should fix the error, however the logic of your query is likely flawed. You are after one row only (select top 1), however you don't define what row should be chosen first (no order by clause).

Upvotes: 1

Related Questions