sql server 2008 - Update Table B With Max(MOD_DATE) From Table A -

table has multiple records each having mod_date , can view max(mod_date) following query:

select max(mod_date) 'mod_date', tablename hemanightly group tablename 

but i'd update table "maxmoddate" these 2 fields: "mod_date" , "tablename" table stored single record contains max(mod_date) hemanightly.

how should go this?

thanks,

try this. work duplicated max date:

declare @t table(id int, d date, n varchar(100))  insert @t values (1, '20150101', 'hemanightly'), (2, '20150105', 'hemanightly'), (3, '20150109', 'hemanightly'), (4, '20150110', 'hemadailyly'), (5, '20150111', 'hemadailyly'), (6, '20150111', 'hemadailyly')  ;with cte (select *, row_number() over(partition n order d desc, n) rn @t) delete cte rn <> 1  select * @t 

output:

id  d           n 3   2015-01-09  hemanightly 5   2015-01-11  hemadailyly