Adding new column (with inner join) and insert (update) value with case when (SQL Server 2012) -

what best approach add column existing table values i've following tables:

table_a clientid, statementid, custbuy  table_b newclientid 

i want add new column ytd_jan13

alter table_a add ytd_jan13 varchar(20)  select  (case     when custbuy=0 , statementid>='01.01.2013' 'new ytd'     else 'repead ytd' end) ytd_jan13 inner join b on a.clientid = b.newclientid 

basically want insert value in new column (ytd_jan13) following conditions: if 1) table_a.clientid = table_b.newclientid 2) in table_a (custbuy=0 , statementid>='01.01.2013') 'new ytd' else 'repead ytd'

i'm confused, i'm asking in advance br, habib

alter table_a add ytd_jan13 varchar(20) 

then stick 'else' value in

update table_a set ytd_jan13 = 'repeat ytd' 

then (this called update join)

update    set   a.ytd_jan13 = 'new ytd'    table_a  inner join b  on a.clientid = b.clientid custbuy = 0 , statementid >= '01.01.2013' 

nb there assumption there no records clients in not in b. if there you'll have bit cleverer ytd_jan13 left null in cases.