create an update query on this.. help

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

create an update query on this.. help

Author

Topic

neo_phyte
Starting Member

10 Posts

Posted - 2011-02-25 : 03:54:08

Want to create an update query...


prs_ID		prt_ID	prs_startDate		prs_endDate		prs_reactivated
60		37	2/24/2011 2:56:00 PM	2/24/2012 2:56:00 PM	False
61		37	2/24/2012 2:56:00 PM	2/24/2013 2:56:00 PM	False
62		37	2/24/2013 2:56:00 PM	2/24/2014 2:56:00 PM	False
63		37	2/24/2014 2:56:00 PM	2/24/2015 2:56:00 PM	False

will changed to


prs_ID		prt_ID	prs_startDate		prs_endDate		prs_reactivated
60		37	2/24/2011 2:56:00 PM	2/24/2012 2:56:00 PM	False
61		37	2/24/2012 2:56:00 PM	2/24/2013 2:56:00 PM	True
62		37	2/24/2013 2:56:00 PM	2/24/2014 2:56:00 PM	True
63		37	2/24/2014 2:56:00 PM	2/24/2015 2:56:00 PM	True

Rules:
Update the rest to True but not the first subscription which is the oldest date

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-25 : 04:01:32

[code]
update t
set prs_reactivated = case when row_no = 1 then 0 else 1 end
from (
select *, row_no = row_number() over (order by prs_startDate)
from thetable
) t
[/code]

KH
[spoiler]Time is always against us[/spoiler]

neo_phyte
Starting Member

10 Posts

Posted - 2011-02-25 : 04:30:27

quote:

Originally posted by khtan


update t
set    prs_reactivated = case when row_no = 1 then 0 else 1 end
from   (
           select *, row_no = row_number() over (order by prs_startDate)
           from   thetable
       ) t

KH
[spoiler]Time is always against us[/spoiler]

This does not work, what if I have this additional data.


46		66	8/1/2011 9:38:00 PM	8/1/2012 9:38:00 PM	False
47		66	8/1/2012 9:38:00 PM	8/1/2013 9:38:00 PM	False

The results would be like this...


prs_ID		prt_ID	prs_startDate		prs_endDate		prs_reactivated
46		66	8/1/2011 9:38:00 PM	8/1/2012 9:38:00 PM	False
47		66	8/1/2012 9:38:00 PM	8/1/2013 9:38:00 PM	True
60		37	2/24/2011 2:56:00 PM	2/24/2012 2:56:00 PM	False
61		37	2/24/2012 2:56:00 PM	2/24/2013 2:56:00 PM	True
62		37	2/24/2013 2:56:00 PM	2/24/2014 2:56:00 PM	True
63		37	2/24/2014 2:56:00 PM	2/24/2015 2:56:00 PM	True

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-25 : 04:50:20

[code]
row_no = row_number() over (partition by prt_ID order by prs_startDate)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

neo_phyte
Starting Member

10 Posts

Posted - 2011-02-25 : 05:10:27

quote:
Originally posted by khtan

row_no = row_number() over (partition by prt_ID order by prs_startDate)

KH
[spoiler]Time is always against us[/spoiler]

It works... Thanks...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources