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 = 18I 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" |
|
|
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. |
|
|
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. |
|
|
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? |
|
|
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. |
|
|
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.DictationsSET [state] = 22WHERE doctorID = 920 AND [state] = 18 What is VoicewareSQL1.dbo.Dictations? A table? A view? E 12°55'05.25"N 56°04'39.16" |
|
|
Chriscpa
Starting Member
10 Posts |
Posted - 2008-04-11 : 16:09:54
|
VoicewareSQL1.dbo.Dictations is a table in the VoicewareSQL1 databaseI tried the following as you suggested:UPDATE VoicewareSQL1.dbo.Dictations SET [state] = 22 Where doctorID = 920 AND [state] = 18And 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. |
|
|
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" |
|
|
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 14Subquery 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. |
|
|
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 |
|
|
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" |
|
|
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? |
|
|
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" |
|
|
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 |
|
|
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 ONGOSET QUOTED_IDENTIFIER ONGOALTER TRIGGER [dbo].[setpriority]on [dbo].[Dictations] AFTER UPDATEAS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for trigger hereDeclare @job intSet @job=(select jobnumber from inserted)update dictations set iprioritylevel=7 where userfield1=7 and jobnumber=@jobEndI 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. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-04-11 : 16:58:51
|
[code]ALTER TRIGGER dbo.setpriority ON [dbo].[Dictations]AFTER UPDATEAS SET NOCOUNT ONUPDATE dSET d.iPriorityLevel = 7FROM Dictations AS dINNER JOIN inserted AS i ON i.JobNumber = d.JobNumberWHERE d.UserField1 = 7 [/code] E 12°55'05.25"N 56°04'39.16" |
|
|
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? |
|
|
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" |
|
|
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" |
|
|
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. |
|
|
Next Page
|