SQL Server: IF EXISTS ; ELSE

Try this:

Update TableB Set
  Code = Coalesce(
    (Select Max(Value)
    From TableA 
    Where Id = b.Id), 123)
From TableB b

EDIT

I want to add the reason that your IF statement seems to not work. When you do an EXISTS on an aggregate, it's always going to be true. It returns a value even if the ID doesn't exist. Sure, it's NULL, but its returning it. Instead, do this:

if exists(select 1 from table where id = 4)

and you'll get to the ELSE portion of your IF statement.

---

Now, here's a better, set-based solution:

update b
  set code = isnull(a.value, 123)
from #b b
left join (select id, max(value) from #a group by id) a
  on b.id = a.id
where
  b.id = yourid

This has the benefit of being able to run on the entire table rather than individual ids.