question

Bone12-2270 avatar image
0 Votes"
Bone12-2270 asked Viorel-1 edited

Remove records from a table that are in another table

Hi,

I have a table (CTL_Sup) which has 25k records all with a Cust_ID

I have another table (Cont_all) which has 450k records all with a Cust_ID

Within the Cont_all table, we have the 25k records included that are in the CTL_Sup and what I want to do, is remove these 25k accounts from the Cont_all table

Any idea how I would write a query to do this please? I hope this makes sense,

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 edited

Consider three of possible approaches:

 delete from a
 from Cont_all a 
 inner join CTL_Sup s on s.Cust_ID = a.Cust_ID

or

 delete from a 
 from Cont_all a
 where exists (select * from CTL_Sup where Cust_ID = a.Cust_ID)

or

 delete from Cont_all
 where Cust_ID in (select Cust_ID from CTL_Sup)


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.