Error in Update Statement

Last Post 31 May 2008 03:29 AM by sjpaq. 5 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
sjpaq
New Member
New Member

--
30 May 2008 01:52 PM
I am executing the following update statement

update dbo.tableA
set proc_long_descr = c.pc_mnc
from
dbo.tableA p, dbo.tableB c
where p.proc_cat_urn = c.proc_cat_urn

and I get the following error message:
21000(512)[Microsoft][ODBC SQL Server Driver][SQL Server]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
01000(3621)[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated. (0.06 secs)

Basically I am trying to update a field on tableA with a lookup value in tableB and each entry in tableA has only one entry in tableB. As proof of this, the following sql gives no rows

select p.proc_urn, count(*)
from
dbo.tableA p, dbo.tableB c
where p.proc_cat_urn = c.proc_cat_urn
group by p.proc_urn
having count(*) >1

Any help is appreciated.
sjpaq
New Member
New Member

--
30 May 2008 04:16 PM
I figured it out, there is a trigger on the table that fires on update / insert that sets an updated timestamp field on the table. So update statements will not work on that table. I guess I need to create a cursor to run the update.

Any other suggestions?
sjpaq
New Member
New Member

--
31 May 2008 03:29 AM
Thanks Russel. I'm still having problems. The trigger code is:

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

I still don't understand what is causing the error message. I bundled the update statement to work on a single row and this worked but when I execute that update statement for a single row inside a cursor loop, I still get the error message.

Any idea what other DB object would execute and cause the update to fail.

Again any help is appreciated.
sjpaq
New Member
New Member

--
31 May 2008 07:12 AM
Let me restate the problem, when I run the following

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
and p.proc_urn = 3

it works fine. When I remove the "and p.proc_urn = 3" to update all rows in the table, 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."
sjpaq
New Member
New Member

--
01 Jun 2008 06:43 AM
Thanks for the help Pete and Russel. All sugestions still give the same error message. Gotta be something with related triggers.
sjpaq
New Member
New Member

--
01 Jun 2008 04:17 PM
The database is provided by an application vendor and so the trigger is theirs and not mine to modify.

I prefer not to disable it either.

I have solved the problem by executing the single update statement inside a cursor loop. This worked fine.

Thanks for the help.


Acceptable Use Policy
---