SQLTeam.com | Weblogs | Forums

Subquery returned more than 1 value. This is not permitted when the subquery follows


#1

I'm getting a Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression eeror when running an update statement.

it's pretty basic and does not contain a subquery:

UPDATE IDO SET IDO.PARTNO = 'MIS56265' WHERE IDO.PARTNO = '047-000009-0001'
UPDATE IDO SET IDO.PARTNO = 'MIS56266' WHERE IDO.PARTNO = '047-000013-0000'

any suggestions are appreciated.

Thanks!


#2

The must be a trigger on the table that has the bad code in it.


#3

how do i figure out where?


#4

Look for triggers on the table then find the one that is an update trigger.


#5

okay i found it, but now what, how can I bypass? I don't want to alter the trigger code.


#6

You'll need to make a minor alteration to the trigger code, but it won't affect how it works for every other UPDATE.

Add a "bypass" option based on the CONTEXT_INFO() value. Then, before your UPDATE, set the bypass on. Afterward, set it back off.

ALTER TRIGGER dbo.trigger_name
ON dbo.IDO
AFTER UPDATE
AS
SET NOCOUNT ON;
IF LEFT(CONTEXT_INFO(), 2) = 0xffff
    RETURN;
...rest of trigger as before...


SET CONTEXT_INFO 0xffff
UPDATE IDO SET IDO.PARTNO = 'MIS56265'	WHERE IDO.PARTNO = '047-000009-0001'
SET CONTEXT_INFO 0x00

#7

sorry for any dumb questions, i have never even heard of triggers until yesterday. but what and where do I paste the above code?

thanks!


#8

I figured it out, thanks so much for your help and time!