Azulitabijou Azulitabijou - 2 months ago 7
SQL Question

SQL - subquery is not introduced with EXISTS

I am trying to update all

creditCounts
except for the first (lowest) account number, however I keep receiving this error:


Msg 116, Level 16, State 1, Line 8

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.


Here is the query:

update Recog
set livesCount = 0
where RECID in (select
r.empNumber, r.acctNbr, r.creditCount, r.groupAcctNumber, r.groupType
from
Recog r
where
creditCount > 0
and policyNbr in
(
(Select acctNbr from Recog)
except
(Select MIN(acctNbr)
from Recog
Group By groupAcctNumber, groupType)
)
)


How do I fix it?

Answer

As the error states, you are SELECTing multiple columns in the subquery and it only makes sense to select one...

update Recog
    set livesCount = 0
    where RECID in (
    select r.RECID
    from Recog r
          where creditCount > 0 and
          policyNbr in ((Select acctNbr from Recog)
          except (Select MIN(acctNbr) from Recog Group By groupAcctNumber,groupType)))

To drive it home, imagine what your statement would look like if you substituted column data in place of your subselect...

update Recog
    set livesCount = 0
    where RECID in (1, 'Account 123', 12, 'Group 123', 'Type X') -- Makes no sense