Want to delete data from the 2 nd tables which data are not in 1st table without using any cursor.

Posted by Ray.Chayan under Sql Server on 9/20/2012 | Points: 10 | Views : 631 | Status : [Member] | Replies : 1
I have 2 tables named T1 and T2.

Both of them have 3 identical columns named C1, C2 and C3. All 3 columns have created a composite key.
I want to delete data from the 2 nd tables which data are not in 1st table without using any cursor.
For your better understanding I am providing some sample values.
The data in the columns are given bellow.


Table: T1

C1 C2 C3
------------------
1 A X
1 B Y
2 C Z
1 A Z



Table: T2
C1 C2 C3
-----------------
1 A X
1 B Y
2 C Z
1 A Z
1 B X

Now I want to delete data(rows) from the T2 where the C1 value is 1.
and all the 3 columns values are not same.

Like in the above example the last row in the T2 table
there C1 values is 1. and in Table1 there is no row with that data value.

So here my target row to delete from Table T2 is that row. This may be more than 1 row also.

Please help me.




Responses

Posted by: Jigarbagadai on: 9/27/2012 [Member] Starter | Points: 25

Up
0
Down
With CTEDelete AS
(
Select * From T2
EXCEPT
Select * From T1

)

Delete From T2
Where c1 in (Select c1 From CTEDelete)
and C2 in(Select C2 From CTEDelete) and C3 in(Select C3 From CTEDelete)



Jigar Bagadai

Ray.Chayan, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response