MERGE Statement

MERGE BookInventory bi

USING BookOrder bo

ON bi.TitleID = bo.TitleID

WHEN MATCHED THEN

  UPDATE

  SET bi.Quantity = bi.Quantity + bo.Quantity;

------------------- ejemplos --------------------------------------

MERGE INTO SALES.MYORDERS WITH(HOLDLOCK) AS Target                

USING Markit.Orders AS Source

ON Target.ORDERID = Source.ORDERID                          

WHEN MATCHED THEN UPDATE

SET Target.ORDERDATE = Source.ORDERDATE;

MERGE INTO SALES.MYORDERS WITH(HOLDLOCK) AS TGT

USING @MYORDERS AS SRC

ON TGT.ORDERID = SRC.ORDERID

WHEN MATCHED THEN UPDATE

SET TGT.ORDERDATE = SRC.ORDERDATE

WHEN NOT MATCHED THEN INSERT

VALUES (SRC.ORDERID, SRC.CustId, src.EmpId, SRC.Orderdate);

MERGE INTO SALES.MYORDERS WITH(HOLDLOCK) AS TGT

USING @MYORDERS AS SRC

ON TGT.ORDERID = SRC.ORDERID

WHEN MATCHED AND (  TGT.CUSTID   <>     SRC.CUSTID   OR (TGT.CUSTID IS NULL AND SRC.CUSTID IS NOT NULL) 

                            OR (TGT.CUSTID IS NOT NULL AND SRC.CUSTID IS NULL)

                            OR    TGT.EMPID    <>     SRC.EMPID 

                            )          

THEN UPDATE

SET TGT.ORDERDATE   =      SRC.ORDERDATE,

       TGT.CUSTID          =      SRC.CUSTID,

       TGT.EMPID           =      SRC.EMPID

WHEN NOT MATCHED THEN INSERT

VALUES (SRC.ORDERID, SRC.CustId, src.EmpId, SRC.Orderdate)

WHEN NOT MATCHED BY SOURCE THEN DELETE;

MERGE BookInventory bi

USING BookOrder bo

ON bi.TitleID = bo.TitleID

WHEN MATCHED AND

  bi.Quantity + bo.Quantity = 0 THEN

  DELETE

WHEN MATCHED THEN

  UPDATE

  SET bi.Quantity = bi.Quantity + bo.Quantity

WHEN NOT MATCHED BY TARGET THEN

  INSERT (TitleID, Title, Quantity)

  VALUES (bo.TitleID, bo.Title,bo.Quantity)

WHEN NOT MATCHED BY SOURCE

  AND bi.Quantity = 0 THEN

  DELETE;