| Author |
Topic |
|
JohnJohn73
Yak Posting Veteran
57 Posts |
 Posted - 2007-10-16 : 13:58:39
|
| hello all!I am trying to perform an update in my database where several items are updated depending on two criteria, the orderID and the name of the item.Here is my update command:UPDATE OperationsInProgressSET IncludeOperation = 1WHERE (OrderID = @OrderID) AND (Name = @Name)I get the following error.SQL Execution ErrorExecuted SQL statement: UPDATE OperationsInProgress SET IncludeOperation=1 WHERE OrderID=@orderID AND Name=@NameError Source: .NET SQLClient Data ProviderError Message: Subquery returned more than 1 value. This is not permitted when the subquery follows=, !=, <, <=, > , >= or when the subquery is used as an expression.So, I am not sure how to update only the items that match a certain order and a certain name. Is there a different way to go about this or is my syntax wrong?Any help would be greatly appreciated! |
|
|
bobspeaking
Starting Member
11 Posts |
Posted - 2007-10-16 : 14:04:45
|
| Hi John!Are you sure that error is related to this query?Normally I encounter this error when I try to set a value from a subquery that returns more row.For example (stupid example) if I run :declare @bob varcharset @bob = (select name from customers)In your case the query must update ALL RECORDS that have orderID = @orderID and name =@Name.I don't see subquery or single value set statement.I hope I am not missing anything... :) |
 |
|
|
JohnJohn73
Yak Posting Veteran
57 Posts |
Posted - 2007-10-16 : 14:13:47
|
| OK, this is where I am confused. Where exactly is the subquery? It doesn't show up in the Update Command, although in Visual studio it is a select command that appears after the Update command.How do I manipulate it? |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-10-16 : 14:14:09
|
| He hasn't included the whole sql statement he is running. His update statement has no subquery in it. Please post the entire sql script you are running. |
|
|
|
JohnJohn73
Yak Posting Veteran
57 Posts |
Posted - 2007-10-16 : 14:21:22
|
| UPDATE OperationsInProgressSET IncludeOperation = 1WHERE (OrderID = @OrderID) AND (Name = @Name)Somewhere I have a gap, but I don't know what. When I get the info from the DB, I am only select distinct values (e.g. the names of operations included in an order.). However, each order may have several operations of the same name, but they'll have different barcode numbers and an ID to distinguish them. So I think that's why I'm getting several results from the subquery. |
|
|
|
bobspeaking
Starting Member
11 Posts |
Posted - 2007-10-16 : 14:46:57
|
| John,probably the application is passing another statement or istruction to sql server.The error that you have posted isn't printed by sql management studio...do you get it from visual studio during development? or from asp.net/windows exception?I ask you this because I suppose that the single update you have posted is correct and the error is related to another problem or statement.Try to start sql profiler and trace all sql command (or stored procedure) and verify what is exactly passed to db :) |
|
|
|
JohnJohn73
Yak Posting Veteran
57 Posts |
Posted - 2007-10-16 : 14:50:01
|
| Thanks Bob and Van.Well it seems like the client has come up with another major change, so this is the least of my problems at this point.I'll get back to you guys! |
|
|
|
evilDBA
Posting Yak Master
155 Posts |
Posted - 2007-10-16 : 15:08:19
|
| DO you have a trigger on that table? |
|
|
|
JohnJohn73
Yak Posting Veteran
57 Posts |
Posted - 2007-10-16 : 15:29:32
|
| Yes, I do. How does that affect this update? |
|
|
|
JohnJohn73
Yak Posting Veteran
57 Posts |
Posted - 2007-10-16 : 15:30:42
|
| Sheez, I hope that isn't the reason. I need that trigger for another table! |
|
|
|
JohnJohn73
Yak Posting Veteran
57 Posts |
Posted - 2007-10-16 : 15:33:42
|
| Are you saying that the subquery is coming from the Trigger code? That could be the error I'm getting. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-16 : 16:11:13
|
| Conversation continued over here:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=91087 |
|
|
|
|
How can I accomplish this update?