Solved

TSQL Update Field Older Than 5 Days

Posted on 2016-10-03
10
44 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
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 69

Assisted Solution

by:Éric Moreau
Éric Moreau earned 125 total points
ID: 41826814
do you have a trigger on the UsersAnswer table?
0
 
LVL 32

Accepted Solution

by:
ste5an earned 250 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 65

Assisted Solution

by:Jim Horn
Jim Horn earned 125 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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 7

Author Comment

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

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 18

Expert Comment

by:Pawan Kumar Khowal
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Copy Database Wizard Error 3 22
Auditing in Azure SQL Database 3 28
Report Builder 9 31
SQL Split character from numbers 3 18
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now