?
Solved

TSQL Update Field Older Than 5 Days

Posted on 2016-10-03
10
Medium Priority
?
74 Views
Last Modified: 2016-10-07
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.
0
Comment
Question by:concretesailors
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 20

Expert Comment

by:Russ Suter
ID: 41826803
Instead of
SELECT @MyDate = DateAdd(DD, -5, GETDATE())
Use
SET @MyDate = DateAdd(DD, -5, GETDATE())
0
 
LVL 7

Author Comment

by:concretesailors
ID: 41826813
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
 
LVL 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 500 total points
ID: 41826814
do you have a trigger on the UsersAnswer table?
0
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
LVL 35

Accepted Solution

by:
ste5an earned 1000 total points
ID: 41826818
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
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 500 total points
ID: 41826822
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
 
LVL 7

Author Comment

by:concretesailors
ID: 41826828
That's what it was a Trigger was stopping, or providing the error.
0
 
LVL 35

Expert Comment

by:ste5an
ID: 41826830
Then correct it.
1
 
LVL 7

Author Comment

by:concretesailors
ID: 41826857
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
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41827288
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
 
LVL 7

Author Closing Comment

by:concretesailors
ID: 41834132
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

Featured Post

More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question