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?