SQL Server : Update Flag on Max Date on Foreign key -

SQL Server : Update Flag on Max Date on Foreign key -

i'm trying update reason cannot quite master sql sub queries.

my table construction follows:

id fk date activeflg --- -- ------- --------- 1 1 04/10/11 0 2 1 02/05/99 0 3 2 09/10/11 0 4 3 11/28/11 0 5 3 12/25/98 0

ideally set activeflg 1 of distinct foreign keys recent date. instance after running query id 1,3 , 4 have active flag set one.

the closest thing came query returning of max dates each distinct fk:

select max(date) table grouping fk

but since cant come subquery there no way can proceed :/

can please give me insight on this. i'm trying larn more sub queries explanation appreciated.

thank you!

you need select fk , restrict that, so

select fk,max(date) table grouping fk

to

with ones2update ( select fk,max(date) table grouping fk ) update table set active=1 table t bring together ones2update u on t.fk = u.fk , t.date = u.date

also test first query first

with ones2update ( select fk,max(date) table grouping fk ) selct fk, date, active table t bring together ones2update u on t.fk = u.fk , t.date = u.date

to create sure getting expect , did not create typos.

additional note: utilize bring together instead of sub-query -- logically same find joins clearer (once got used using joins). depending on optimizer can faster.

sql sql-server sql-update inner-join