how to Delete where multiple columns IN\NOT IN other table?

问题: I have a target Table and a source table and I want to delete a row from it only if some of the column's data appear in another table (the source table) for example:...

问题:

I have a target Table and a source table

and I want to delete a row from it only if some of the column's data appear in another table (the source table)

for example:

Target Table to delete from it:

|  Delivery  |   Order     |     Row      |  OtherColumn |
|:----------:|:-----------:|:------------:|:------------:|
|     1      |     A       |    000010    |     asaf     |
|     1      |     A       |    000020    |     ag       |
|     2      |     C       |    000010    |     DEL      |<-I WANT TO DELETE THIS ROW
|     2      |     D       |    000020    |     hhs      |  
|     3      |     E       |    000040    |     ba       |
|     3      |     E       |    000170    |     ass      |

Source table, Here I have a row that i want to KEEP in the target table :

|  Delivery  |   Order     |     Row      |
|:----------:|:-----------:|:------------:|
|     2      |     D       |    000020    | 

the result I'm looking for the target table is:

|  Delivery  |   Order     |     Row      |  OtherColumn |
|:----------:|:-----------:|:------------:|:------------:|
|     1      |     A       |    000010    |     asaf     |
|     1      |     A       |    000020    |     ag       |
|     2      |     D       |    000020    |     hhs      |  
|     3      |     E       |    000040    |     ba       |
|     3      |     E       |    000170    |     ass      |

Meaning, all the rows for delivery number 2 would be deleted if they are not in the source table

I have the Delivery parameter as @Delivery

what I tried is:

delete from Target 
where Delivery = @Delivery
and ([Delivery],[Order],[Row]) not in (select [Delivery],[Order],[Row] 
                                 from Source)

but there's a syntax error, I understand multiple columns are not acceptable in NOT IN operator,

what can i do?


回答1:

Try to use NOT EXISTS:

delete from Target 
from Target target
where Delivery = @Delivery
and NOT EXISTS (select 1 from Source s where s.[Delivery] = target.Delivery and 
    s.[Order] = target.[Order] and s.[Row] = target.[Row] )

回答2:

Can you try the below query.

DELETE from Target WHERE Delivery = @Delivery AND NOT EXists(SELECT 1 from Source 
WHERE Delivery =Target.Delivery AND Order=Target.Order and Row=Target.Row )
  • 发表于 2019-02-17 21:17
  • 阅读 ( 163 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除