quote:

---

Originally posted by sjpaq@rogers.com

I was trying to execute the following update statement

update dbo.t1
set proc_long_descr = c.pc_mnc
from
dbo.t1 p, dbo.t2 c
where p.proc_cat_urn = c.proc_cat_urn

I am basically updating a description field from a lookup into another table. This normally works without a problem. However I get the following error.

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

This is related to the fact that my table t1 above has the following trigger defined on it.

CREATE TRIGGER dbo.tr_t1_ins_upd ON dbo.t1
FOR INSERT, UPDATE AS
Update ORprocedure
set t1.mod_timestamp_server = getdate()
from inserted i
where t1.proc_urn = i.proc_urn

So I tried to create a cursor loop script where within each iteration of the cursor I update a row. However, I still get the same message.

Any help is appreciated.

---

update t1 set
	t1.mod_timestamp_server = getdate()
from	inserted i
inner join ORPRocedure t1
		on t1.proc_urn = i.proc_urn

--This assumes that proc_urn is the primary key of ORPRocedure (or has a Unique constraint on it)

update p set
	p.proc_long_descr = c.pc_mnc
from	dbo.t1 p
inner join dbo.t2 c
		on c.proc_cat_urn = p.proc_cat_urn 

If Update(proc_mnc) OR Update(proc_descr) OR Update(proc_active) OR Update(proc_cat_urn) OR 
Update(proc_major_minor) OR Update(meddiag_urn) OR Update(proc_cpt) OR Update(proc_long_descr)

BEGIN

Select @mnemonic = proc_mnc,
@active = proc_active,
@descr = proc_descr,
@proc_cat = (Select pc_mnc 
From proc_category a,
inserted b
Where a.proc_cat_urn = b.proc_cat_urn),
@major_minor = proc_major_minor,
@setup_time = (Select ps_setup_dur
From proc_surg_dur a,
inserted b
Where fac_urn = 0 AND
surg_urn = 0 AND
a.proc_urn = b.proc_urn),....