Solved

TSQL Update Field Older Than 5 Days

Posted on 2016-10-03
10
55 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 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 125 total points
ID: 41826814
do you have a trigger on the UsersAnswer table?
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 33

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
 
LVL 7

Author Comment

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

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 28

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

777 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