sql server - T-SQL NOT IN works, but NOT EXISTS does not -

as new records added table, need mark old records 'level' old. can not in, not exists not work. suspect has subquery correlation explained here: not exists query doesn't work on informix while same query not in works don't understand why & use further explanation. here's example code:

create table t2 (id int, level int, somedate datetime, mostrecent int) go insert t2 select 1, 1, '1/1/2010', 1 union select 2, 1, '2/2/2010', 1 union select 3, 2, '3/3/2010', 1 union select 4, 3, '4/4/2010', 1 union select 5, 3, '5/5/2010', 1 union select 6, 3, '6/6/2010', 1 union select 7, 4, '7/7/2010', 1 union select 8, 5, '8/8/2010', 1 union select 9, 6, '9/9/2010', 1 union select 10, 6, '10/10/2010', 1 union select 11, 8, '11/11/2012', 1 go -- doesn't work update t2 set mostrecent = 0 t2 not exists     (select * t2     join         (select level, max(somedate) somedate t2 group level) maxrows     on t2.somedate = maxrows.somedate) go -- works update t2 set mostrecent = 0 t2 id not in     (select t2.id t2     join         (select level, max(somedate) somedate t2 group level) maxrows     on t2.somedate = maxrows.somedate) 

your query wrong, needs be:

update t set mostrecent = 0 t2 t not exists     (select *       (select level, max(somedate) somedate t2 group level) maxrows       t.somedate = maxrows.somedate) 

i've added alias tables keep clear

in original query, not linking not exists query update statement, therefore return results, , "exist" every record in table updated.