Update - Sub Query Error

Question

Update - Sub Query Error

norbertackerman

SSC Eights!

Points: 945
More actions
May 17, 2010 at 4:16 pm

#219017

I need to update a table. My update query is the following:
UPDATE membership
SET [non_member] = 1
WHERE (expire_date < getdate() or expire_date is NULL) and ((last_visited_date < DATEADD(MM, -6, CURRENT_TIMESTAMP))
or (last_visited_date is NULL))
GO
For some reason 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.
The statement has been terminated.
Not sure how to fix this.
Thank you,
Norbert

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Your query looks fine to me. Are you sure it is comming from this query? Is there maybe another query near this one that could be causing the error?

Here is an example that I mocked up to attempt to reproduce your error. Can you alter the example to get it to error?

DECLARE @membership TABLE(MemberID int IDENTITY(1,1), member_name varchar(50), non_member bit, expire_date datetime, last_visited_date datetime)

INSERT INTO @membership(member_name, non_member, expire_date, last_visited_date)

SELECT 'john rowan', 0, NULL, NULL UNION ALL

SELECT 'jen rowan', 0, DATEADD(dd, 1, getdate()), NULL UNION ALL

SELECT 'katie rowan', 0, DATEADD(dd, -10, getdate()), NULL

UPDATE @membership

SET [non_member] = 1

WHERE (expire_date < getdate() or expire_date is NULL) and ((last_visited_date < DATEADD(MM, -6, CURRENT_TIMESTAMP))

or (last_visited_date is NULL))

SELECT *

FROM @membership

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

The code you have posted should not be generating this type of error. Is that the entirety of the update statement?

And now that I see Johns response - what he said;-)

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

David Webb-CDS SSCoach Points: 17398 More actions · Answer 3

Is there an update trigger on the table? If so, you might want to make sure it can handle multiple row updates.

And then again, I might be wrong ...
David Webb

norbertackerman SSC Eights! Points: 945 More actions · Answer 4

I did not create this database. There may be a trigger on this table.

Is there a way to temporarily disable the trigger while I do the update?

Thank you.

norbertackerman SSC Eights! Points: 945 More actions · Answer 5

I found the answer. I temporarily disabled the trigger on that table:

--ALTER TABLE [membership] DISABLE TRIGGER ALL

-- do work here

--ALTER TABLE [membership] ENABLE TRIGGER ALL

Thank you everyone for your help!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

So it turned out to be a trigger. Thanks for posting back with the solution.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

John Rowan SSC Guru Points: 56440 More actions · Answer 7

This still leaves you with a poorly coded trigger. I would recommend fixing the trigger code as well. This is a common, but unfortunate, mistake in trigger coding. Whoever created the trigger designed it to only work with single row updates. This is a design flaw and should be fixed. If you want to post the trigger code, we can help you with that.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden