TSQL Update Field Older Than 5 Days

I am having horrible issues with this Update query (TSQL MS SQL 2008r2)

------ This should update all the Frozen fields where Frozen = 1 and FrozenDate <= Today minus 5 -------------
DECLARE @MyDate DATETIME
SELECT @MyDate = DateAdd(DD, -5, GETDATE())

UPDATE dbo.UsersAnswer
SET Frozen = 0
Where FrozenDate <= @MyDate

However, I am receiving this error:

Msg 512, Level 16, State 1, Procedure TR_FrozenDate, Line 8
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.

I started with a subselect in the WHERE Clause, but altered the script to what you see above.
While I may SELECT all the rows which fit this description, I can not update the dbo.Frozen field.
Any help would be great appreciated.
Thank you.
LVL 7
El ConquistadorSenior DBAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Russ SuterCommented:
Instead of
SELECT @MyDate = DateAdd(DD, -5, GETDATE())
Use
SET @MyDate = DateAdd(DD, -5, GETDATE())
0
 
El ConquistadorSenior DBAAuthor Commented:
Msg 512, Level 16, State 1, Procedure TR_FrozenDate, Line 8
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.

Same error with SET as SELECT
This is the data my variable is holding
Sep 28 2016 12:46PM
0
 
Éric MoreauSenior .Net ConsultantCommented:
do you have a trigger on the UsersAnswer table?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ste5anSenior DeveloperCommented:
As your UPDATE is correct, this means there is a trigger active on  dbo.UsersAnswer, which is poorly written.

Triggers are called per statement. Thus the tables INSERTED and DELETED exist in the trigger scope. The most common error is that beginners only test with single row updates and have a trigger like:

CREATE TRIGGER ON tableNama
AFTER UPDATE
AS
  SET NOCOUNT ON;
  DECLARE @someVariable INT;
  SET @someVariable = ( SELECT columnName FROM INSERTED).

Open in new window


which now in the case of a multi-row update fails.
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Five bucks says the error is not in the code you posted, as I don't see any subqueries in play here unless dbo.UsersAnswer has a trigger or is a view that has a subquery, so let's do this:

  • Run it again, get the error again.
  • Double-click on the error message and watch the cursor jump to the offending line.
  • Copy-paste that line + whatever relevant code is above that line into this question, using a code block, and explicitly tell us which line is the error.
0
 
El ConquistadorSenior DBAAuthor Commented:
That's what it was a Trigger was stopping, or providing the error.
0
 
ste5anSenior DeveloperCommented:
Then correct it.
1
 
El ConquistadorSenior DBAAuthor Commented:
Thanks guys, I thought I had looked for the trigger, but obviously I hadn't checked.
1/2 day wasted on this query, and I should have checked again for the trigger, as I did when everyone mentioned it.
Thanks :)
0
 
Pawan KumarDatabase ExpertCommented:
Can you post the trigger code here, we shall have a look and change it for you !

To check what triggers are there use below-

SSMS -> Databases -> Database -> Table > Triggers
0
 
El ConquistadorSenior DBAAuthor Commented:
Replaced Trigger with SQL Job which runs the Update Statement every ten minutes.
The trigger was supposed to do the Update when the criteria was met, but seemed to never work correctly.
Thank you for your assistance.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.