update problem when subquery returns multiple results 1

[cwbrumett]

I'm having a weird problem, I'm trying to update a table where the pkey is InvItemNum and InvCompanyCode (on both tables) ... the problem is when I try to execute the below statement it returns the error

Server: Msg 512, Level 16, State 1, Procedure trg_UpdateItem, 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.

but if I do a

SELECT InvItemNum, InvCompanyCode  FROM SISMaster..SIS_Inventory_Master

it returns 1503 records
and if I do

SELECT DISTINCT InvItemNum, InvCompanyCode  FROM SISMaster..SIS_Inventory_Master

it returns 1503 records ... I don't get how it can return multiple results from the subquery because there is NO duplicated data (as well as, it's the pkey I'm joining on)

here's the update statement that is producing the error,

update SISMaster..SIS_Inventory_Master 
set 
	SISMaster..SIS_Inventory_Master.InvItemDesc = M.InvItemDesc,
	SISMaster..SIS_Inventory_Master.InvProductGrp = M.InvProductGrp,
	SISMaster..SIS_Inventory_Master.InvWeight = M.InvWeight,
	SISMaster..SIS_Inventory_Master.InvAverageCost = M.InvAverageCost,
	SISMaster..SIS_Inventory_Master.InvCasesPerPallet = M.InvCasesPerPallet,
	SISMaster..SIS_Inventory_Master.InvStandardCost = M.InvStandardCost

FROM SISTrx..SIS_Inventory_Master M
WHERE 
	M.InvItemNum=SISMaster..SIS_Inventory_Master.InvItemNum
	AND M.InvCompanyCode=SISMaster..SIS_Inventory_Master.InvCompanyCode

thanks for any help, I'm so lost right now on this one.

 

[philhege]

How many records do you get back with the joined SELECT statement?

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Eschewing obfuscation diurnally.

 

[vongrunt]

There are no subqueries in code above.

Maybe there are some triggers below the surface?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[vongrunt]

Yup:

> Server: Msg 512, Level 16, State 1, Procedure [!]trg_[/!]UpdateItem, Line 4

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[cwbrumett]

AHHHHH! forgot to check for triggers! that was it, my brain has been fried all morning on why that wouldn't work.

THANK YOU!