sql - Using multiple statements in WHEN MATCHED -


i using merge statement in order insert xml input sql server database table. how execute multiple conditions in when matched block. please refer below code.

using tablerelationship new   on (new.tablerelationshiptypeid = old.tablerelationshiptypeid) , old.toroleid = @roleid  when matched     update         set old.fromroleid = new.fromroleid    -- condition 2    -- condition 3 

currently when matched executes old.fromroleid = new.fromroleid line. how can execute 3 lines (-- condition 2 , 3) inside when not matched condition.

ex :

this expect. when matched want update old field (old.thrudate = getdate()) , insert record same table. cant separate statements comma. sql emits

incorrect syntax

merge party.tablerelationship old using tablerelationship new on (new.tablerelationshiptypeid = old.tablerelationshiptypeid) , old.toroleid = @roleid  when matched     update         set old.thrudate = getdate(),    insert (fromroleid, toroleid, tablerelationshiptypeid)    values (new.fromroleid, new.toroleid, new.tablerelationshiptypeid); 

thank you.

you use insert on dml achieve it:

insert tab_name(fromroleid, toroleid, tablerelationshiptypeid) select fromroleid, toroleid, tablerelationshiptypeid (   merge party.tablerelationship old   using tablerelationship new       on new.tablerelationshiptypeid = old.tablerelationshiptypeid      , old.toroleid = @roleid   when matched      update set old.thrudate = getdate()     output $action, fromroleid, toroleid, tablerelationshiptypeid ) sub(action, fromroleid, toroleid, tablerelationshiptypeid) action = 'update'; 

keep in mind method has limitations more info: ms connect


Comments

Popular posts from this blog

resizing Telegram inline keyboard -

command line - How can a Python program background itself? -

php - "cURL error 28: Resolving timed out" on Wordpress on Azure App Service on Linux -