use tempdb
go

create table dbo.suppliers (
   supplier_ID int not null,
   supplier_name varchar(200) not null,
   constraint pk_suppliers primary key clustered (supplier_id)
);

create table dbo.supplierproducts (
   supplier_id int not null,
   product_code varchar(20) not null,
   product_name varchar(200) not null,
   constraint pk_products primary key clustered (supplier_id, product_code),
   constraint fk_products_supplier foreign key (supplier_id) references dbo.suppliers (supplier_id) on delete cascade on update cascade not for replication
);

insert dbo.suppliers( supplier_id, supplier_name)
values (1, 'supplier one'),
   (2, 'supplier two'),
   (3, 'supplier three');
go

insert dbo.supplierproducts( supplier_id, product_code, product_name)
values (1, 'apple', 'red apples'),
   (1, 'pear', 'red pears'),
   (1, 'rose', 'red roses'),
   (2, 'apple', 'white apples'),
   (2, 'pear', 'white pears'),
   (3, 'apple', 'blue apples'),
   (3, 'pear', 'blue pears');
go

-- Show the content of supplierproducts table before the merge.
select * from dbo.supplierproducts
go


-- Here's the new product list supplier 1 has just sent us.
-- Notice that this list only includes supplier 1's products,
-- not the products of any other suppliers.
declare @updatedproductlist table (
   product_code varchar(20) not null,
   product_name varchar(200) not null,
   primary key (product_code)
);

insert @updatedproductlist( product_code, product_name)
values ('apple', 'red apples'),
   ('pear', 'green pears'),
   ('kiwi', 'green kiwis');


declare @supplier_id int;

select @supplier_id = 1;

with cteTarget as (
   select sp.supplier_id,
      sp.product_code,
      sp.product_name
   from dbo.supplierproducts sp
   where sp.supplier_id = @supplier_id
)
merge into cteTarget trg
using @updatedproductlist src
   on (src.product_code = trg.product_code)
when not matched by target
then
   insert( supplier_id, product_code, product_name)
   values( @supplier_id, src.product_code, src.product_name)
   
when matched and src.product_name <> trg.product_name
then
   update 
   set product_name = src.product_name
when not matched by source
then 
   delete
output $action, inserted.*, deleted.*;


-- Show the content of supplierproducts table before the merge.
-- Note that the products of supplier 2 and 3 are unchanged.
select * from dbo.supplierproducts;

WHEN NOT MATCHED BY SOURCE AND T.TypeID = 1