need help with rollover update

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

General SQL Server Forums

New to SQL Server Programming

need help with rollover update

Author

Topic

wooldawg
Starting Member

12 Posts

Posted - 2008-04-24 : 09:43:39

Hello all,

I'm not even sure what you call this kind of update statement so I wasn't able to search the forums before making new post.

Say I have a table looks like this:

Name: Year:
Bob 2007
Sue 2007
Jane 2007
Ralph 2008
Sue 2008
Jane 2008

What I need to do is insert all the names from the most current year back into the table with the next year so that after the update the table would look like:

Name: Year:
Bob 2007
Sue 2007
Jane 2007
Ralph 2008
Sue 2008
Jane 2008
Ralph 2009
Sue 2009
Jane 2009

This seems simple enough but has me completely stumped. Thanks for your help!

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-24 : 09:47:28

[code]
declare @t table (name_col varchar(10), year_col int)

insert into @t
select 'bob',2007
union all select 'sue',2007

insert into @t
select name_col,max(year_col)+1
from @t
group by name_col

select * from @t
[/code]

Em

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-24 : 09:52:35

INSERT Table1 (Name, Year)
SELECT Name, DATEPART(YEAR, GETDATE())
FROM Table1 WHERE Year = DATEPART(YEAR, GETDATE()) - 1

E 12°55'05.25"
N 56°04'39.16"

wooldawg
Starting Member

12 Posts

Posted - 2008-04-24 : 10:15:17

Thanks Peso, this is exactly what I was looking for.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources