Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

TSQL Update Field Older Than 5 Days

Posted on 2016-10-03
10
Medium Priority
?
83 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 500 total points
ID: 41826814
do you have a trigger on the UsersAnswer table?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 36

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 36

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 38

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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

571 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