| Author |
 Topic  |
|
Chriscpa
Starting Member
USA
10 Posts |
 Posted - 04/11/2008 : 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
Sweden
29138 Posts |
Posted - 04/11/2008 : 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
USA
10 Posts |
Posted - 04/11/2008 : 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
USA
4128 Posts |
Posted - 04/11/2008 : 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
India
47139 Posts |
Posted - 04/11/2008 : 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
USA
10 Posts |
Posted - 04/11/2008 : 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
Sweden
29138 Posts |
Posted - 04/11/2008 : 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" |
|
|
|
Chriscpa
Starting Member
USA
10 Posts |
Posted - 04/11/2008 : 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.
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 04/11/2008 : 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
USA
10 Posts |
Posted - 04/11/2008 : 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. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 04/11/2008 : 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
Sweden
29138 Posts |
Posted - 04/11/2008 : 16:39:29
|
True. That possibility missed my attention.
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
Chriscpa
Starting Member
USA
10 Posts |
Posted - 04/11/2008 : 16:41:57
|
| Yes there is a trigger on that table. is it possible to work around that? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 04/11/2008 : 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)
USA
6997 Posts |
Posted - 04/11/2008 : 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
USA
10 Posts |
Posted - 04/11/2008 : 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. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 04/11/2008 : 16:58:51
|
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
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
Chriscpa
Starting Member
USA
10 Posts |
Posted - 04/11/2008 : 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
Sweden
29138 Posts |
Posted - 04/11/2008 : 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
Sweden
29138 Posts |
Posted - 04/14/2008 : 05:40:25
|
How did your daughter's soccer game go?
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
Chriscpa
Starting Member
USA
10 Posts |
Posted - 04/15/2008 : 12:21:57
|
Thank you so much for your help, I got the trigger installed and was able to run the Update query.
|
|
|
|
Topic |
|
Update query
