StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><code>DELETE ... FROM</code> is a T-SQL extension to the standard SQL <code>DELETE</code> that provides an alternative to using a subquery. From the help:</p> <blockquote> <p>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.</p> <pre><code>-- 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 </code></pre> </blockquote> <p>You would want, in your second solution, something like</p> <p>-- untested! DELETE FROM dbo.SubProtocols -- ProtocolHeaders, etc FROM dbo.SubProtocols INNER JOIN @Protocols ON SubProtocols.ProtocolID = @Protocols.ProtocolID</p> <h2><strong>However!!</strong></h2> <p>Is it not possible to alter your design so that all the susidiary protocol tables have a <code>FOREIGN KEY</code> with <code>DELETE CASCADE</code> to the main <code>Protocols</code> table? Then you could just <code>DELETE</code> from <code>Protocols</code> and the rest would be taken care of...</p> <p><em>edit</em> to add:</p> <p>If you already have <code>FOREIGN KEY</code>s 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 <code>DELETE CASCADE</code> turned on. Once that is in place, a <code>DELETE</code> from the main table will automatically <code>DELETE</code> related records from the child table.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload