Delete from source using MERGE command in SQL Server -

Delete from source using MERGE command in SQL Server -

i have temp table info in @temptable need compare info table name lu_bcoms_usersrepfamilies

by using @temptable info need replace in master table lu_bcoms_usersrepfamilies based on status next columns

1) repfamily_repfamilyid 2) users_userid

my query listed below :

declare @temptable table(repfamily_repfamilyid int,users_userid varchar(500),createddate varchar(500),createdby varchar(500)) insert @temptable values('676','mv10310','2014-05-20 13:30:29.000','mv10310') merge lu_bcoms_usersrepfamilies target using @temptable source on target.repfamily_repfamilyid = source.repfamily_repfamilyid , target.users_userid = source.users_userid when matched update set target.repfamily_repfamilyid = source.repfamily_repfamilyid, target.users_userid = source.users_userid, target.createddate = source.createddate, target.createdby = source.createdby when not matched target insert (repfamily_repfamilyid, users_userid, createddate,createdby) values (source.repfamily_repfamilyid, source.users_userid, source.createddate,source.createdby) when not matched source delete target.repfamily_repfamilyid = source.repfamily_repfamilyid , target.users_userid = source.users_userid;

after execute query getting next error

error : wrong syntax near keyword 'where'.

can modify query according requirement specified above

example : repfamily_repfamilyid users_userid createddate createdby 600 mv10310 date ravi 601 mv10310 date ravi 602 kv110 date suma 603 gv122 date rajesh in temp table have insert @temptable values('600','mv10310','date','ravi') after merging master table show below : repfamily_repfamilyid users_userid createddate createdby 600 mv10310 date ravi 602 kv110 date suma 603 gv122 date rajesh

here answer..

merge lu_bcoms_usersrepfamilies target using @temptable source on target.repfamily_repfamilyid = source.repfamily_repfamilyid , target.users_userid = source.users_userid when matched update set target.repfamily_repfamilyid = source.repfamily_repfamilyid, target.users_userid = source.users_userid, target.createddate = source.createddate, target.createdby = source.createdby when not matched target insert (repfamily_repfamilyid, users_userid, createddate,createdby) values (source.repfamily_repfamilyid, source.users_userid, source.createddate,source.createdby) when not matched source , target.users_userid = (select top 1 (a.users_userid) @temptable a) delete ;

sql sql-server merge sql-server-2008-r2