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.
| Author |
Topic |
|
slihp
Yak Posting Veteran
61 Posts |
 Posted - 2014-06-16 : 14:40:04
|
| Hi there im not sure how to do this as im not great with update statements. I have a table that get populated with related ids. basically i want to periodicaly update the table and set the related ids to the last know id that was entered, so in the case belowthis set ID | LinkID | Active | Datez5 | k2 | 0 | 01/01s6 | k2 | 0 | 17/02d7 | k2 | 0 | 21/03d8 | k2 | 0 | 20/04d9 | k2 | 1 | 02/05would be updated to thisID | LinkID | Active | Dated9 | k2 | 0 | 01/01d9 | k2 | 0 | 17/02d9 | k2 | 0 | 21/03d9 | k2 | 0 | 20/04d9 | k2 | 1 | 02/05 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-06-16 : 15:06:55
|
| [code]--create table/sample datacreate table #temp (ID char(2), LinkID char(2), Active bit, [Date] datetime)insert into #temp values ('z5', 'k2', 0, '01/01/14')insert into #temp values ('s6', 'k2', 0, '02/17/14')insert into #temp values ('d7', 'k2', 0, '03/21/14')insert into #temp values ('d8', 'k2', 0, '04/20/14')insert into #temp values ('d9', 'k2', 1, '05/02/14')insert into #temp values ('a4', 'k9', 0, '01/01/14')insert into #temp values ('z8', 'k9', 1, '05/17/14')--the part you care about;with cte1 (LinkID, [Date])as( select LinkID, max([Date]) from #temp group by LinkID),cte2 (ID, LinkID, [Date])as( select t.ID, cte1.LinkID, t.[Date] from #temp t join cte1 on t.LinkID = cte1.LinkID and t.[Date] = cte1.[Date])update tset ID = cte2.IDfrom #temp tjoin cte2 on t.LinkID = cte2.LinkID and t.[Date] <> cte2.[Date]--validate updateselect * from #temp--cleanupdrop ttable #temp[/code]Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
|
|
|
|
|
Help with Update statment