left join另个表记录不存在就删除

MS_MedicinePurchase进药表、BS_Product商品表

SELECT MS_MedicinePut.* FROM dbo.MS_MedicinePut
LEFT JOIN BS_Product ON MS_MedicinePut.ProductID=BS_Product.ProductID AND iSort=2
WHERE MS_MedicinePut.bUse=0 AND BS_Product.ProductID IS NULL

删除

BEGIN TRAN
UPDATE MS_MedicinePut SET bUse=1 FROM dbo.MS_MedicinePut
LEFT JOIN BS_Product ON MS_MedicinePut.ProductID=BS_Product.ProductID AND iSort=2
WHERE MS_MedicinePut.bUse=0 AND BS_Product.ProductID IS NULL
ROLLBACK TRAN
COMMIT TRAN