Update a table based on person ID and date

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 2008 Forums

Transact-SQL (2008)

Update a table based on person ID and date

Author

Topic

nhsHL7rtm
Starting Member

1 Post

Posted - 2014-09-24 : 15:51:52

I have been tasked to update a column to a certain value based on a ID.

For example we have 600 records in a table. We have some records that have the same ID(not the primary key column)

So we have something like this
PKID ID Date1 Date2 MainNumber
1 234 1/1/2014 NULL 888888
4 234 2/4/2014 NULL 777777
19 234 5/6/2014 NULL 898899
23 435 2/5/2014 NULL 234532
56 435 8/2/2014 NULL 234802

I want to run an update query to set the Date2 value to something(doesn't matter what it is) based on the ID and we can't update the record with the latest Date1 date. So for example, records 1 and 4 need to be updated with a value for Date2 but record 19 can not be updated at all. Do we have to do multiple joins on the single table?

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-09-24 : 19:02:09

[code]declare @tbl table (
pkid int not null,
id int not null,
Date1 date not null,
Date2 date null,
MainNumber int not null
)

insert into @tbl (
pkid,
id,
Date1,
Date2,
MainNumber
)
values
(1, 234, '1/1/2014', NULL, 888888),
(4, 234, '2/4/2014', NULL, 777777),
(19, 234, '5/6/2014', NULL, 898899),
(23, 435, '2/5/2014', NULL, 234532),
(56, 435, '8/2/2014', NULL, 234802)

/**/select 'Before', * from @tbl

;with OrderByDate
as (
select t.*, row_number() over (partition by id order by Date1 DESC) rn
from @tbl t
where t.id = 234
)
update obd
set Date2 = '17760704'
from OrderByDate obd
where obd.rn <> 1

select 'After', *
from @tbl[/code]

Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources