Update Column Based On Dates - SQL Server Forums

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

Update Column Based On Dates

Author

Topic

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-05-26 : 10:13:37

I have a database, that has multiple records, that can be updated at different times, and these are by date column.
I wanted to know if there is a way i can run a query to look at all the records basked on the users username, and update a column with the data that was updated latest.

So the query should run, look at all records that have the same username, then look at all the sis_datecreated, and look for the one that is the latest date, and then update the latest_rec_flag column with 'yes', by default that column is no

Table Layout
id - sis_datecreate - username - latest_rec_flag

Table Sample Data
1 - 12:00 09/19/2000 - joe1 - no
2 - 12:00 10/22/2000 - joe1 - no
3 - 12:00 11/16/2010 - joe1 - no
4 - 12:00 08/19/2000 - joe2 - no
5 - 12:00 09/19/2000 - joe2 - no

Results should look like
1 - 12:00 09/19/2000 - joe1 - no
2 - 12:00 10/22/2000 - joe1 - no
3 - 12:00 11/16/2010 - joe1 - yes
4 - 12:00 08/19/2000 - joe2 - no
5 - 12:00 09/19/2007 - joe2 - yes

Any help would be great, I am horrible at these types of queries.

Sachin.Nand

2937 Posts

Posted - 2010-05-26 : 10:26:49

[code]
;with cte
as
(
select flag,ROW_NUMBER()over(PARTITION by username order by sis_datecreate desc)as rid from yourtable
)
update cte set flag='yes' where rid=1
select * from yourtable
[/code]

P.S PLEASE TEST IN TEST ENVIROMENT FIRST

PBUH

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-26 : 10:28:18

Try:
update dt
set latest_rec_flag='yes'
from
(select id, sis_datecreate, username, latest_rec_flag,
rownumber() over (partition by username order by sis_datecreate desc) as rownum)dt
where rownum=1

No, you're never too old to Yak'n'Roll if you're too young to die.

Sachin.Nand

2937 Posts

Posted - 2010-05-26 : 10:38:34

quote:
Originally posted by webfred

Try:
update dt
set latest_rec_flag='yes'
from
(select id, sis_datecreate, username, latest_rec_flag,
rownumber() over (partition by username order by sis_datecreate desc) as rownum)dt
where rownum=1

No, you're never too old to Yak'n'Roll if you're too young to die.

I doubt it will work.My guess is it will update all the columns to 'Yes' as there is no relation with the derived table.

PBUH

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-26 : 10:40:11

quote:
Originally posted by Idera

quote:
Originally posted by webfred

Try:
update dt
set latest_rec_flag='yes'
from
(select id, sis_datecreate, username, latest_rec_flag,
rownumber() over (partition by username order by sis_datecreate desc) as rownum)dt
where rownum=1

No, you're never too old to Yak'n'Roll if you're too young to die.

I doubt it will work.My guess is it will update all the columns to 'Yes' as there is no relation with the derived table.

PBUH

But there is a relation because I am updating directly the derived table

No, you're never too old to Yak'n'Roll if you're too young to die.

Sachin.Nand

2937 Posts

Posted - 2010-05-26 : 10:41:05

Let me try.

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-05-26 : 10:48:06

Oop's my bad.It is working.Missed the derived table name in update part.Sorry.


declare @tbl as table(id int,dt date,flag varchar(5))
insert into @tbl
select 1,GETDATE(),'no' union all
select 1,GETDATE()-1,'no'union all
select 2,GETDATE()-3,'no'union all
select 2,GETDATE()-4,'no'

update dt
set flag='yes'
from (select id, flag,ROW_NUMBER() over (partition by id order by dt desc) as rownum from @tbl t1)dt where rownum=1

select * from @tbl

PBUH

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-26 : 10:49:49

no problem

No, you're never too old to Yak'n'Roll if you're too young to die.

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-05-26 : 11:01:11

lol so what one do i use lol

webfred
Master Smack Fu Yak Hacker

8781 Posts