update using most recent - 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 using most recent

Author

Topic

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2012-11-07 : 19:45:08

Lets say John Smith is in the table 11/7/2012 and 10/7/2012. I want to append the newest date to the file. How would I do that?


update a set
       a.date_added = m.date
from append.dbo.[100_K021034571] a
join main.rez.NEWCONNECTS m
       on SOUNDEX(a.ln) = SOUNDEX(m.ln)
       and SOUNDEX(a.fn) = SOUNDEX(m.fn)
       and left(a.addr,10) = left(m.addr,10)
       and a.zip = m.zip

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-07 : 23:40:59

update a set
a.date_added = case WHEN m.date > a.date_added THEN m.date ELSE a.date_added end
from append.dbo.[100_K021034571] a
join main.rez.NEWCONNECTS m
on SOUNDEX(a.ln) = SOUNDEX(m.ln)
and SOUNDEX(a.fn) = SOUNDEX(m.fn)
and left(a.addr,10) = left(m.addr,10)
and a.zip = m.zip

--
Chandu

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2012-11-08 : 00:16:01

i'm sorry, probably didnt explain that well. basically say we have 1 entry in the append.dbo.[100_K021034571] (date is currently NULL) table, but i have 2 entries in the main.rez.NEWCONNECTS table. one from 11/7/2012, and another from 10/7/2012. i basically wanna ensure that append.dbo.[100_K021034571].Date_added = 11/7/2012, and it doesnt use 10/7/2012 instead. make sense?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-08 : 00:51:12

Tr this................

;with cte
as ( SELECT ln, fn, addr, zip, [date], ROW_NUMBER() over(PARTITION BY ln, fn, addr, zip ORDER BY [date] DESC) rn
from main.rez.NEWCONNECTS
)
update a set
a.date_added = c.[date]
from append.dbo.[100_K021034571] a
join cte c
on SOUNDEX(a.ln) = SOUNDEX(c.ln)
and SOUNDEX(a.fn) = SOUNDEX(c.fn)
and left(a.addr,10) = left(c.addr,10)
and a.zip = c.zip
AND c.rn = 1

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources