Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Update query

Author  Topic 

Chriscpa
Starting Member

10 Posts

Posted - 2008-04-11 : 13:51:50
I am trying to update a field based on two conditions.

UPDATE VoicewareSQL1.dbo.Dictations
SET state = 22
Where doctorID = 920 and state = 18

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.

I tried making the conditons a subquery, but could not get that to work either.

Thanks in advance for assistance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-11 : 14:25:05
There are no subqeuries in the post above.
Please post the REAL query you are using.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Chriscpa
Starting Member

10 Posts

Posted - 2008-04-11 : 14:39:54
UPDATE VoicewareSQL1.dbo.Dictations
SET state = 22
Where EXISTS (Select doctorID, state
from VoicewareSQL1.dbo.Dictations
Where doctorID = 920 and state = 18)

returns 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.

Go to Top of Page

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2008-04-11 : 15:08:06
(Moved to a new forum)

=================================================
Creating tomorrow's legacy systems today. One crisis at a time.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-11 : 15:12:29
thats because you get more than one set of values from subquery. which one dod you want to update to your table? first,last or random?
Go to Top of Page

Chriscpa
Starting Member

10 Posts

Posted - 2008-04-11 : 15:45:22
Im not sure I understand. I want to find all records Where doctorID = 920 and state = 18 and change the state value to 22.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-11 : 15:54:48
There are still no subqueries that would produce the error you mention!
UPDATE	VoicewareSQL1.dbo.Dictations
SET [state] = 22
WHERE doctorID = 920
AND [state] = 18

What is VoicewareSQL1.dbo.Dictations? A table? A view?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Chriscpa
Starting Member

10 Posts

Posted - 2008-04-11 : 16:09:54
VoicewareSQL1.dbo.Dictations is a table in the VoicewareSQL1 database

I tried the following as you suggested:
UPDATE VoicewareSQL1.dbo.Dictations
SET [state] = 22
Where doctorID = 920
AND [state] = 18

And I still get the same error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-11 : 16:22:00
It is impossible to get the error your describe with MICROSOFT SQL Server 2005,
if "VoicewareSQL1.dbo.Dictations" is a user table.

"state" and "doctorID" are normal columns, not calculated columns?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Chriscpa
Starting Member

10 Posts

Posted - 2008-04-11 : 16:37:07
yes, the properties are:
state (smallint, null)
doctorid (nvarchar(10), null)

I am sorry, but I just realized that I did not get the entire error message posted. In full it is:

Msg 512, Level 16, State 1, Procedure setpriority, Line 14
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.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-04-11 : 16:37:25
I would look to see if there is an update trigger on the table. That could be the source of the error.


CODO ERGO SUM
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-11 : 16:39:29
True. That possibility missed my attention.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Chriscpa
Starting Member

10 Posts

Posted - 2008-04-11 : 16:41:57
Yes there is a trigger on that table. is it possible to work around that?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-11 : 16:44:25
No. The trigger is fired no matter what (unless yuo disable it in sql server 2005).
There is a reason for the trigger to be there.

I think the designer of the system never realized that an update could stretch more than 1 record.
Post the code of the trigger and we can see if it can be rewritten to manage multi-row updates.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-04-11 : 16:45:55
It is probably badly written and not designed to handle more than one row being updated at a time.



CODO ERGO SUM
Go to Top of Page

Chriscpa
Starting Member

10 Posts

Posted - 2008-04-11 : 16:54:44
I did not write it, but it is as follows:

USE [VoicewareSQL1]
GO
/****** Object: Trigger [dbo].[setpriority] Script Date: 04/11/2008 15:49:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER TRIGGER [dbo].[setpriority]
on [dbo].[Dictations]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for trigger here
Declare @job int
Set @job=(select jobnumber from inserted)

update dictations set iprioritylevel=7 where userfield1=7 and jobnumber=@job
End

I thank you for your help. I would also appreciate any suggestions you might have to modify this trigger so that it did allow more than one row to update at a time.
I have to leave for a couple hours, my daughter has a soccer match.
Thanks again.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-11 : 16:58:51
[code]ALTER TRIGGER dbo.setpriority ON [dbo].[Dictations]
AFTER UPDATE
AS

SET NOCOUNT ON

UPDATE d
SET d.iPriorityLevel = 7
FROM Dictations AS d
INNER JOIN inserted AS i ON i.JobNumber = d.JobNumber
WHERE d.UserField1 = 7 [/code]


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Chriscpa
Starting Member

10 Posts

Posted - 2008-04-11 : 17:09:58
I hate to be naive, but does that replace the whole trigger i posted or just the Alter Trigger part?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-11 : 17:26:25
Alter trigger replaces whole trigger code content.
So with my suggestion the complete trigger is rewritten to handle multi-row updates.

Or are there more code in the trigger than you posted earlier?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 05:40:25
How did your daughter's soccer game go?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Chriscpa
Starting Member

10 Posts

Posted - 2008-04-15 : 12:21:57
Thank you so much for your help, I got the trigger installed and was able to run the Update query.
Go to Top of Page
    Next Page

- Advertisement -