sql server - T-SQL Update Record need to add condition which requires JOIN -

i have existing query updates specific values (represented col1, col2, col3 here) of records in table current values of cols defined source record in table. source record defined id foreign key in table (represented fkrecid here)

declare @sourcerecordid varchar(50) set @sourcerecordid= n'{the id}'  update dbo.targettable    set [col1] = sourcedata.col1       ,[col2] = sourcedata.col2       ,[col3] = sourcedata.col3      ( select col1, col2, col3 targettable fkrecid = @sourcerecordid ) sourcedata ((fkrecid != @sourcerecordid) ,  (cast(targettable.col1 nvarchar(max)) != cast(sourcedata.col1 nvarchar(max)) or  cast(targettable.col2 nvarchar(max)) != cast(sourcedata.col2 nvarchar(max)) or  cast(targettable.col3 nvarchar(max)) != cast(sourcedata.col3 nvarchar(max)) )  go 

this updates other records in table using values defined in source record. however, need add further condition updates records same "type" source record. value of "type" held in table - (the primary key of table entry foreign key id mentioned above).

i have attempted modify query incorporate join have limited t-sql experience , have been struggling syntax correct. portion have tried update shown below

     ( select col1, col2, col3 targettable fkrecid = @sourcerecordid ) sourcedata join dbo.othertable t on t.primarykey = targettable.fkrecid ((fkrecid != @sourcerecordid) , (t.type = knowntype) , 

this leads error in sql server query editor @ targettable element of join statement. if put in column name intellisense error shows "invalid column name", if qualify table name becomes "the multi part identifier xxx not bound".

any on i'm going wrong syntax appreciated, i'd appreciate if point me towards online reference resource examples of t-sql statements goes beyond basics may haev fiound answer in.

thanks.

you should use update statement this:

declare @sourcerecordid varchar(50) set @sourcerecordid= n'{the id}'  update u set u.[col1] = sourcedata.col1    ,u.[col2] = sourcedata.col2    ,u.[col3] = sourcedata.col3 dbo.targettable u      inner join dbo.othertable t on t.primarykey=u.fkrecid , t.type=knowntype      inner join dbo.targettable sourcedata on sourcedata.fkrecid=@sourcerecordid u.fkrecid!=@sourcerecordid ,        (cast(u.col1 nvarchar(max)) != cast(sourcedata.col1 nvarchar(max)) or         cast(u.col2 nvarchar(max)) != cast(sourcedata.col2 nvarchar(max)) or         cast(u.col3 nvarchar(max)) != cast(sourcedata.col3 nvarchar(max)) ) 

basically, , clause identical select statement. in first line of update statement need mention alias of table want update , in set part specify columns of alias on left side of equals sign.

if col1, col2 , col3 columns nullable, have take care compare's work correctly in case of null's.