February 8, 2011 at 2:12 pm
I have a table with a DateTime2 column named EndDateTime. I'm trying to subtract 1 second from all of them. I get the error below. I don't understand the problem. I'm sure it's something simple, but I don't know what it is:
update ProctorSlot set EndDateTime = DATEADD(second, -1, EndDateTime)
generates this error:
Msg 512, Level 16, State 1, Procedure ProctorSlot_PreventOverlap, Line 25
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
.
February 8, 2011 at 3:27 pm
Gosh BSavoie, I tried it out and it all works okay for me.
Here is my test case:
create table #myTemp (
row_id INT IDENTITY( 1,1) NOT NULL,
myTime DATETIME2 NOT NULL
)
INSERT #myTemp ( myTime ) VALUES ( GETDATE())
SELECT * FROM #myTemp
UPDATE #myTemp
SET myTime = DATEADD( second, -1, myTime )
SELECT * FROM #myTemp
DROP TABLE #myTemp
It almost seems to me like your error message is originating somewhere else. I didn't see any sub-queries in your code, and the DATEADD( ) function works with either DATETIME or DATETIME2 datatypes.
February 8, 2011 at 3:33 pm
Well that's strange. But now that you mention it, I think this query worked in the past.
.
February 8, 2011 at 8:19 pm
There must be a conflict in the data, but I'm not seeing it.
.
February 9, 2011 at 3:57 am
I suspect that a trigger has been added to that table, and the trigger either includes a subquery or does not not allow for multiple rows being updated in some way.
February 9, 2011 at 1:38 pm
You are exactly right Mike! Thank You!!
.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply