[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Sql Query date change query

Posted on 2011-05-09
7
Medium Priority
?
252 Views
Last Modified: 2012-05-11
I have a query I am trying to get to work.

update table set EndDate =
      (SELECT
      DATEADD(dd, -1, EndDate) as final FROM
      table)
where id in (select id from table)

when I run it I get

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 was given this query for another purpose and it works well.
Please give insight to what I might be missing.
Thank You
0
Comment
Question by:jeremy4help
7 Comments
 
LVL 11

Assisted Solution

by:Deepak Lakkad
Deepak Lakkad earned 800 total points
ID: 35720081
Hi

below is the revised version of your query

update table
set EndDate = (SELECT DATEADD(dd, -1, EndDate) as final FROM table WHERE T.ID = Table.ID)
FROM table t
where id in (select id from table)

Check it out

- Deepak Lakkad
0
 
LVL 9

Assisted Solution

by:mayank_joshi
mayank_joshi earned 1200 total points
ID: 35720126
i think you are using only one table .so why dont you simply do this:-

update table set EndDate = DATEADD(dd, -1, EndDate) where id is not null

Open in new window

0
 
LVL 11

Accepted Solution

by:
Deepak Lakkad earned 800 total points
ID: 35720147
Hi,

you can get same result with following query if you are your "table" refers one table.

update Table
set EndDate = DATEADD(dd, -1, EndDate)
where id in (select id from table)


- Deepak Lakkad
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
LVL 9

Assisted Solution

by:mayank_joshi
mayank_joshi earned 1200 total points
ID: 35720164
i cant understand what's the purpose of:-

where id in (select id from table)

Open in new window

when both id 's are  from the same table.
it as good as
 where id is not null

Open in new window

0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35720187
Please give some additional filter or use Top 1
0
 

Author Comment

by:jeremy4help
ID: 35720216
Thank You all.
The top one worked fine can you explain breifly what I missed?
0
 
LVL 9

Assisted Solution

by:mayank_joshi
mayank_joshi earned 1200 total points
ID: 35720250
jeremy4help:
if you are using only one table in your query a simple update will do:-

update table set EndDate = DATEADD(dd, -1, EndDate) 

Open in new window


or

update table set EndDate = DATEADD(dd, -1, EndDate) where id is not null

Open in new window


0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Screencast - Getting to Know the Pipeline

834 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