August 25, 2010 at 2:27 pm
I have a situation where I want to delete a number of rows from a table, using 1 query for efficiency. In cases where I have a single-column primary key, I can use this:
DELETE FROM MY_TABLE WHERE MY_COL IN ('A','B','C','D','E');
I'm not sure what to do in cases where I have a multiple-column primary key. Is there any way to batch them all together or do I need multiple DELETE statements? For example, say there are rows identified by the primary keys:
A,1
A,2
A,3
A,4
B,1
B,2
B,3
I can't easily do this using IN() and I don't really want to have 7 DELETE statements:
DELETE FROM MY_TABLE WHERE MY_COL = 'A' AND MY_COL = '1';
DELETE FROM MY_TABLE WHERE MY_COL = 'A' AND MY_COL = '2';
...
Can you think of an efficient way to accomplish the 2nd task?
August 25, 2010 at 2:32 pm
If you have a table with the values to be deleted (or can create a temporary one) you could just Inner Join on both fields.
August 25, 2010 at 2:38 pm
The joining on the delete would go like this:
DECLARE @TempTable TABLE (col1 char(1), col2 int);
INSERT INTO @TempTable
select 'A',1 UNION ALL
SELECT 'A',2 UNION ALL
SELECT 'A',3 UNION ALL
SELECT 'A',4 UNION ALL
SELECT 'B',1 UNION ALL
SELECT 'B',2 UNION ALL
SELECT 'B',3;
DECLARE @MyTable TABLE (col1 char(1), col2 int);
INSERT INTO @MyTable
select 'A',1 UNION ALL
SELECT 'A',2 UNION ALL
SELECT 'A',3 UNION ALL
SELECT 'A',4 UNION ALL
SELECT 'A',5 UNION ALL -- extra row
SELECT 'B',1 UNION ALL
SELECT 'B',2 UNION ALL
SELECT 'B',3 UNION ALL
SELECT 'B',4 UNION ALL -- extra row
SELECT 'B',5; -- extra row
-- shows what's here initially
select * from @MyTable;
DELETE FROM @MyTable
FROM @MyTable mt
JOIN @TempTable tt
ON tt.col1 = mt.Col1
AND tt.col2 = mt.Col2;
-- show what's left after the delete
select * from @MyTable;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 25, 2010 at 2:41 pm
If you have a table with the values to be deleted (or can create a temporary one) you could just Inner Join on both fields.
Well, the values to be deleted are being parsed out of a file. If I first INSERT'ed them into a temporary table in preparation for a DELETE, I think that would usually be even slower than just issuing a bunch of individual DELETE statements.
Although, now that I think about it, a BULK INSERT followed by a bulk DELETE could be faster than a series of individual DELETE's. I will keep that in mind.
August 25, 2010 at 2:42 pm
e1785 (8/25/2010)
If you have a table with the values to be deleted (or can create a temporary one) you could just Inner Join on both fields.
Well, the values to be deleted are being parsed out of a file. If I first INSERT'ed them into a temporary table in preparation for a DELETE, I think that would usually be even slower than just issuing a bunch of individual DELETE statements.
Although, now that I think about it, a BULK INSERT followed by a bulk DELETE could be faster than a series of individual DELETE's. I will keep that in mind.
Have you got a sample of the file? It might not be so bad...
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 25, 2010 at 2:45 pm
How many rows need to be deleted here? And how many rows in the table? It would have to be a massive amount for performance to be an issue in this case.
August 25, 2010 at 3:26 pm
Have you got a sample of the file? It might not be so bad...
The files are CSV-style like:
"Andrew"|"Smith"|"39"|"Horseback Riding"
"Elaine"|"Jones"|"22"|"Hiking"
Looking at how much data processing I have to handle, I think that your INNER JOIN solution is probably going to be the only one that scales well, partly just because the only way I can even load all of this data fast enough is with a BULK INSERT.
Can you think of a way to do the following via INNER JOIN in the same way?
merge into tablename
where idfield = 7
when matched then
update
set field1 = 'new value',
field2 = 'different value',
...
when not matched then
insert ( idfield, field1, field2, ... )
values ( 7, 'value one', 'another value', ... )
Taken from http://stackoverflow.com/questions/234/sql-server-2005-implementation-of-mysql-replace-into
August 25, 2010 at 4:24 pm
Well, the MERGE statement does support updates, inserts and deletes all at once. And common-table-expressions (CTE). Between the two, you should be able to get it done with one very efficient statement.
Performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply