Geek Answers Handbook

What is the fastest way to remove all clients from SQL Server based on this client that does not meet any criteria

Let me explain as an example. Consider the following table:

Customer Id | Food ------------+--------- 1 | Pizza 1 | Burger 1 | Hot Dog 2 | Milkshake 2 | Burger 3 | Pizza

I want to delete all entries for customers who have NEVER ordered pizza. So, I have to be left this (customer number 2 removed):

 Customer Id | Food ------------+--------- 1 | Pizza 1 | Burger 1 | Hot Dog 3 | Pizza

I know I can do NOT IN , but the performance is terrible.

What is the most efficient way to write this query to achieve this against 100,000 records in SQL Server?

+7
sql-server tsql sql-server-2008
source share
4 answers

Simple NOT EXISTS should be effective with appropriate indexes.

 DELETE c1 FROM Customers c1 WHERE NOT EXISTS ( SELECT 1 FROM Customers c2 WHERE c1.[Customer Id] = c2.[Customer Id] AND c2.[Food] = 'Pizza' );

Demo

Create an index on [Customer Id] and a non-clustered index on Food .

+9
source share

how about NOT EXISTS

 DELETE a FROM table1 a WHERE NOT EXISTS ( SELECT 1 FROM table1 b WHERE a.customerID = b.customerID AND b.Food = 'Pizza' )
+4
source share

to try

 delete t from t left join (select distinct [Customer Id] from t where Food='Pizza') t2 on t.[Customer Id]=t2.[Customer Id] where t2.[Customer Id] is null

SQLFiddle demo

0
source share
  • If the table size is huge, try deleting lots. or
  • Move entries that do not need to be deleted in the temporary table. Kill the main table, and then move the remaining entries from the temporary table to the main table.
0
source share

More articles:


All Articles