SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Update query
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

Chriscpa
Starting Member

USA
10 Posts

Posted - 04/11/2008 :  13:51:50  Show Profile  Reply with Quote
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
30281 Posts

Posted - 04/11/2008 :  14:25:05  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

USA
10 Posts

Posted - 04/11/2008 :  14:39:54  Show Profile  Reply with Quote
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

USA
4138 Posts

Posted - 04/11/2008 :  15:08:06  Show Profile  Visit graz's Homepage  Reply with Quote
(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

India
52325 Posts

Posted - 04/11/2008 :  15:12:29  Show Profile  Reply with Quote
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

USA
10 Posts

Posted - 04/11/2008 :  15:45:22  Show Profile  Reply with Quote
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

Sweden
30281 Posts

Posted - 04/11/2008 :  15:54:48  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

USA
10 Posts

Posted - 04/11/2008 :  16:09:54  Show Profile  Reply with Quote
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

Sweden
30281 Posts

Posted - 04/11/2008 :  16:22:00  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

USA
10 Posts

Posted - 04/11/2008 :  16:37:07  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 04/11/2008 :  16:37:25  Show Profile  Reply with Quote
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

Sweden
30281 Posts

Posted - 04/11/2008 :  16:39:29  Show Profile  Visit SwePeso's Homepage  Reply with Quote
True. That possibility missed my attention.



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

Chriscpa
Starting Member

USA
10 Posts

Posted - 04/11/2008 :  16:41:57  Show Profile  Reply with Quote
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

Sweden
30281 Posts

Posted - 04/11/2008 :  16:44:25  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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)

USA
7020 Posts

Posted - 04/11/2008 :  16:45:55  Show Profile  Reply with Quote
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

USA
10 Posts

Posted - 04/11/2008 :  16:54:44  Show Profile  Reply with Quote
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

Sweden
30281 Posts

Posted - 04/11/2008 :  16:58:51  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

Chriscpa
Starting Member

USA
10 Posts

Posted - 04/11/2008 :  17:09:58  Show Profile  Reply with Quote
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

Sweden
30281 Posts

Posted - 04/11/2008 :  17:26:25  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Sweden
30281 Posts

Posted - 04/14/2008 :  05:40:25  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

USA
10 Posts

Posted - 04/15/2008 :  12:21:57  Show Profile  Reply with Quote
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
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000