Server :根据TVP表的值删除表中的记录

Question

我有一个PurchaseStationeryDetails和TVP(表值参数) TVP_PurchaseStationeryDetails，，我的问题是->我想要从PurchaseStationeryDetails中删除，这个记录在TVP表中是不可否认的。

​

declare @TVP_PurchaseStationeryDetails as TVP_PurchaseStationeryDetails

考虑一下，我将数据插入到TVP表中。

DELETE  from PurchaseStationeryDetails
            WHERE PurchaseStationeryMasterId = 1003 AND  PurchaseStationeryDetailsId  IN
                    (SELECT PSD.PurchaseStationeryDetailsId FROM PurchaseStationeryDetails PSD, @TVP_PurchaseStationeryDetails TVP_PSD 
                           WHERE PSD.PurchaseStationeryMasterId = TVP_PSD.PurchaseStationeryMasterId AND PSD.PurchaseStationeryDetailsId  ! = TVP_PSD.PurchaseStationeryDetailsId AND TVP_PSD.PurchaseStationeryDetailsId != 0)

PurchaseStationeryDetails--->PurchaseStationeryDetailsId，PurchaseStationeryMasterId.....etc中的列

TVP_PurchaseStationeryDetails--->PurchaseStationeryDetailsId，PurchaseStationeryMasterId.....etc中的列

在这里，我想从->表中删除记录104,2

Answer 1

尝试像这样的

delete from PSD

from PurchaseStationeryDetails PSD
where not exists ( 
                  select 1 from  @TVP_PurchaseStationeryDetails TVP_PSD
                  where PSD.PurchaseStationeryMasterId = TVP_PSD.PurchaseStationeryMasterId
                  and isnull(PSD.PurchaseStationeryDetailsId,0)  = TVP_PSD.PurchaseStationeryDetailsId 
                  )

and PSD.PurchaseStationeryMasterId = 1003 

Answer 2

尝试使用Left或Right Outer Join

 create table #tvp (id int, val int)

insert into #tvp 
values
(101, 1), (102,  2),(103,2)

create table #purchase (iden int, value int)

insert into #purchase 
values
(101, 1), (102,  2),(103,2),(104,2)


delete p
from #tvp t
right outer join
#purchase p 
on (t.val = p.value and t.id = p.iden)
where t.id is null