J'ai un PurchaseStationeryDetails et un TVP (Table valued parameter) TVP_PurchaseStationeryDetails , Ma question est - & gt; Je voulais supprimer l'enregistrement de PurchaseStationeryDetails qui ne sont pas disponibles dans le tableau TVP . entrez la description de l'image ici

declare @TVP_PurchaseStationeryDetails as TVP_PurchaseStationeryDetails

Considérez simplement que j'ai inséré des données dans la table 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)

Colonnes dans PurchaseStationeryDetails --- & gt; PurchaseStationeryDetailsId, PurchaseStationeryMasterId ..... etc.

Colonnes dans TVP_PurchaseStationeryDetails --- & gt; PurchaseStationeryDetailsId, PurchaseStationeryMasterId ..... etc.

Ici, je voulais supprimer l'enregistrement - & gt; 104,2 du tableau PurchaseStationeryDetails

1
user2115618 28 déc. 2015 à 14:31

2 réponses

Meilleure réponse

essayez ceci comme ça

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 
0
DimaSUN 28 déc. 2015 à 12:07

Essayez d'utiliser Left ou 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 
0
bmsqldev 28 déc. 2015 à 13:56