strange error with update query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

strange error with update query

Author

Topic

sth_Weird
Starting Member

38 Posts

Posted - 2009-10-12 : 07:04:03

hi,
I get a strange error when trying to run this update query:
UPDATE MyTable
SET [DoC] = [DoB]
That's the whole query.
What I want to do is simply copy the values from one column to another.
DoC and DoB are two smalldatetime columns. Both can be edited. There are no triggers. None of the columns is indexed or is used as foreign key or marked as unique or whatever.
The error I get is that the subquery returns more than one value and this is not allowed when the subquery follows <, <= , = etc.
I don't understand. I don't have a subquery do I?
What's the correct query to simply copy the values?

thx
sth_Weird

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-10-12 : 07:08:45

hi

Can you post your full query.

Bcoz the error message appears when you try to use subquery (correlated or not) that returns more than one value to the calling query.

-------------------------
R...

sth_Weird
Starting Member

38 Posts

Posted - 2009-10-12 : 08:02:12

thanx for your reply.
the problem is that this is the whole query...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-12 : 08:11:09

Does it return anytype related to trigger?

EXEC sp_depends MyTable

Madhivanan

Failing to plan is Planning to fail

rocknpop
Posting Yak Master

201 Posts

Posted - 2009-10-12 : 08:11:16

are you sure there are no triggers on this table, can't see anything else,very strange. Can you post the table structure, though not sure right now if it would help. Maybe something new to learn here.

--------------------
Rock n Roll with SQL

sth_Weird
Starting Member

38 Posts

Posted - 2009-10-12 : 08:49:40

there is no trigger handling queries like mine.
the only other one there is starts like this:

CREATE TRIGGER insertTexts ON dbo.MyTable 
FOR INSERT 
AS
BEGIN
	IF (SELECT Count(*) FROM inserted) > 1
		RETURN

That means it immediately returns if there are more than one records (which is the case in my query). Besides, it triggers only inserts and my query updates existing records. So I don't think this counts, does it?
MyTable has a primary key, a couple of nvarchar and bit fields, one or two of them are foreign keys from other tables.
And those two smalldatetime-fields, of which one (the one I want to fill) is new.
Actually I was sure I have done something similar like in my query before (copying a column) and it worked.
I dunno. Last week the department which manages the sqlserver made some updates and maintenance jobs. Is it possible that something went wrong there? I dunno about the updates, I'm only using my database and tables and I'm sure they didn't mess with them. But on the other hand I can manually edit selected cells without problems.
Really strange...
sth_Weird

sth_Weird
Starting Member

38 Posts

Posted - 2009-10-12 : 08:57:11

oh shame on me!
there was another (test-)trigger in my test-database which caused the error...and I tried to find the error in the real database.
running EXEC sp_depends MyTable (which was accidently bound to the test-database) did the trick.
sorry for the confusion. Everything works fine now :-)
thanx for your help
sth_weird

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources