ERROR: "Subquery returned more than 1 value."

I've got a big procedure written by a contractor. I'm trying to
execute it (against a test db until I get it worked out) and there is
one section of it that fails. I've isolated the section and run it in
Query Analyzer and it still fails. Here is the SQL:
DELETE FROM tblBedOccupancy WHERE (
ContactID IN (
SELECT ContactID FROM tblPeople WHERE Community IN (
SELECT DISTINCT BuildingID FROM lnkCompaniesBuildings WHERE CompanyID
<> 61
)
)
)
The error returned is:
Server: Msg 512, Level 16, State 1, Procedure tg_DeleteOccupancyRecord,
Line 18
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.
If I change DELETE to SELECT *, it runs fine. If I run the subqueries
they run fine. How can I get this to work?
Thanks,
Josh D> The error returned is:
> Server: Msg 512, Level 16, State 1, Procedure tg_DeleteOccupancyRecord,
> Line 18
> 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.
Look closely at the error message. Did you notice the name
"tg_DeleteOccupancyRecord". What does this refer to? Looks like a
trigger - and a poorly written one to boot. The problem is the code in the
trigger.|||I'd agree with Scott, sounds like you have trigger written by someone who
didn't realize that triggers fire once per statement, not once per row.

> Server: Msg 512, Level 16, State 1, Procedure tg_DeleteOccupancyRecord,
> Line 18
> 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.|||> I'd agree with Scott, sounds like you have trigger written by someone who
> didn't realize that triggers fire once per statement, not once per row.
...and if that was the contractor, this might constitute breach of contract
.
:)
ML|||> ...and if that was the contractor, this might constitute breach of
> contract.
> :)
Well, if the contractor's side of the agreement doesn't explicitly state, "I
know what I'm doing"...|||> Well, if the contractor's side of the agreement doesn't explicitly state, "Id">
> know what I'm doing"...
In continental law the "I know what I'm doing" part is presumed (praesumptio
iuris), and the contractor can only limit his own liability by stating the
opposite.
ML