Using temporary table in where clause

Question

I want to delete many rows with the same set of field values in some (6) tables. I could do this by deleting the result of one subquery in every table (Solution 1), which would be redundant, because the subquery would be the same every time; so I want to store the result of the subquery in a temporary table and delete the value of each row (of the temp table) in the tables (Solution 2). Which solution is the better one?

First solution:

DELETE FROM dbo.SubProtocols
WHERE ProtocolID IN (
    SELECT ProtocolID
    FROM dbo.Protocols
    WHERE WorkplaceID = @WorkplaceID
)

DELETE FROM dbo.ProtocolHeaders
WHERE ProtocolID IN (
    SELECT ProtocolID
    FROM dbo.Protocols
    WHERE WorkplaceID = @WorkplaceID
)

// ...

DELETE FROM dbo.Protocols
WHERE WorkplaceID = @WorkplaceID

Second Solution:

DECLARE @Protocols table(ProtocolID int NOT NULL)

INSERT INTO @Protocols
SELECT ProtocolID
FROM dbo.Protocols
WHERE WorkplaceID = @WorkplaceID

DELETE FROM dbo.SubProtocols
WHERE ProtocolID IN (
    SELECT ProtocolID
    FROM @Protocols
)

DELETE FROM dbo.ProtocolHeaders
WHERE ProtocolID IN (
    SELECT ProtocolID
    FROM @Protocols
)

// ...

DELETE FROM dbo.Protocols
WHERE WorkplaceID = @WorkplaceID

Is it possible to do solution 2 without the subquery? Say doing WHERE ProtocolID IN @Protocols (but syntactically correct)?

I am using Microsoft SQL Server 2005.

Answer 1

While you can avoid the subquery in SQL Server with a join, like so:

delete from sp
from subprotocols sp
inner join protocols p on
    sp.protocolid = p.protocolid
    and p.workspaceid = @workspaceid

You'll find that this doesn't gain you really any performance over either of your approaches. Generally, with your subquery, SQL Server 2005 optimizes that in into an inner join, since it doesn't rely on each row. Also, SQL Server will probably cache the subquery in your case, so shoving it into a temp table is most likely unnecessary.

The first way, though, would be susceptible to changes in Protocols during the transactions, where the second one wouldn't. Just something to think about.

Answer 2

Can try this

DELETE  FROM dbo.ProtocolHeaders
FROM    dbo.ProtocolHeaders INNER JOIN
     dbo.Protocols ON ProtocolHeaders.ProtocolID = Protocols.ProtocolID
WHERE   Protocols.WorkplaceID = @WorkplaceID

Answer 3

Check this: http://stackoverflow.com/questions/439750/t-sql-selecting-rows-to-delete-via-joins

Answer 4

DELETE ... FROM is a T-SQL extension to the standard SQL DELETE that provides an alternative to using a subquery. From the help:

D. Using DELETE based on a subquery and using the Transact-SQL extension The following example shows the Transact-SQL extension used to delete records from a base table that is based on a join or correlated subquery. The first DELETE statement shows the SQL-2003-compatible subquery solution, and the second DELETE statement shows the Transact-SQL extension. Both queries remove rows from the SalesPersonQuotaHistory table based on the year-to-date sales stored in the SalesPerson table.

-- SQL-2003 Standard subquery

USE AdventureWorks;
GO
DELETE FROM Sales.SalesPersonQuotaHistory 
WHERE SalesPersonID IN 
    (SELECT SalesPersonID 
     FROM Sales.SalesPerson 
     WHERE SalesYTD > 2500000.00);
GO



-- Transact-SQL extension
USE AdventureWorks;
GO
DELETE FROM Sales.SalesPersonQuotaHistory 
FROM Sales.SalesPersonQuotaHistory AS spqh
    INNER JOIN Sales.SalesPerson AS sp
    ON spqh.SalesPersonID = sp.SalesPersonID
WHERE sp.SalesYTD > 2500000.00;
GO

You would want, in your second solution, something like

-- untested! DELETE FROM dbo.SubProtocols -- ProtocolHeaders, etc FROM dbo.SubProtocols INNER JOIN @Protocols ON SubProtocols.ProtocolID = @Protocols.ProtocolID

However!!

Is it not possible to alter your design so that all the susidiary protocol tables have a FOREIGN KEY with DELETE CASCADE to the main Protocols table? Then you could just DELETE from Protocols and the rest would be taken care of...

edit to add:

If you already have FOREIGN KEYs set up, you would need to use DDL to alter them (I think a drop and recreate is required) in order for them to have DELETE CASCADE turned on. Once that is in place, a DELETE from the main table will automatically DELETE related records from the child table.

Answer 5

Without the temp table you risk deleting different rows in the the second delete, but that takes three operations to do.

You could delete from the first table and use the OUTPUT INTO clause to insert into a temp table all the IDs, and then use that temp table to delete the second table. This will make sure you only delete the same keys with and with only two statements.

declare @x table(RowID int identity(1,1) primary key, ValueData varchar(3))
declare @y table(RowID int identity(1,1) primary key, ValueData varchar(3))
declare @temp table (RowID int)

insert into @x values ('aaa')
insert into @x values ('bab')
insert into @x values ('aac')
insert into @x values ('bad')
insert into @x values ('aae')
insert into @x values ('baf')
insert into @x values ('aag')

insert into @y values ('aaa')
insert into @y values ('bab')
insert into @y values ('aac')
insert into @y values ('bad')
insert into @y values ('aae')
insert into @y values ('baf')
insert into @y values ('aag')

DELETE @x
    OUTPUT DELETED.RowID
    INTO @temp
    WHERE ValueData like 'a%'

DELETE y
    FROM @y               y
        INNER JOIN @temp  t ON y.RowID=t.RowID

select * from @x
select * from @y

SELECT OUTPUT:

 RowID       ValueData
----------- ---------
2           bab
4           bad
6           baf

(3 row(s) affected)

RowID       ValueData
----------- ---------
2           bab
4           bad
6           baf

(3 row(s) affected)