CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlsql-server-2012
SkyeBoniwell
SkyeBoniwell

Reputation: 7092

complicated select logic with multiple JOINS

I'm trying to work through the logic in a query I'm writing.

I wanted to make sure I have this correct before I use a DELETE.

I want to delete(select) all rows in A where a.xxxId exists in B and b.yyyId exists in C but where c.yyyId does NOT exist in D.

Would this query work?

-- all a that are in b, 
-- that are in c,
-- that are NOT in d

-- change to DELETE

SELECT  a.*
FROM    A a
        JOIN    B b ON a.xxxId = b.xxxId
        JOIN    C c ON b.yyyId = c.yyyId
WHERE NOT EXISTS ( SELECT * FROM D d WHERE c.yyyId = d.yyyId)

Thanks!

Upvotes: 2

Views: 91

Answers (2)

David Rushton
David Rushton

Reputation: 5030

Only you can tell if the query works! Running the query as a SELECT before updating to a DELETE is a good idea. As others have pointed out taking a backup is also a great way of ensuring you can restore any accidental deletions.

You can also use TRANSACTIONS. My example uses this sample data:

Sample Data

/* We'll use a temp table to test the transaction.
 */
CREATE TABLE #Sample    
    (
        Id    INT
    )
;

/* Populate sample values.
 */
INSERT INTO #Sample
    (
        Id
    )
VALUES
    (1),
    (2),
    (3),
    (4),
    (5)
;

Using a TRANSACTION you can execute your query, capture the output and rollback the changes. This example uses the DELETED table created by SQL Server. This is a temporary table, unless you store the results they will be lost.

Example

/* Wrapping your statements in a transaction allows you to 
 * rollback the results.
 */
BEGIN TRANSACTION xy;

    /* The output clause allows you to inspect
     * the deleted records, using the deleted table.
     * This table is created by SQL Server for you.
     */
    DELETE
    FROM
        #Sample
    OUTPUT
        deleted.*
    WHERE
        Id > 3
    ;

ROLLBACK TRANSACTION xy;


    /* Outside the transaction the table still contains the 
     * original records.
     */
    SELECT
        *
    FROM
        #Sample
    ;

Replacing ROLLBACK with COMMIT will make your changes a permanent part of the database.

In practice I'd combined this approach with the others already suggested.

EDIT: In my original version I used a table variable, not knowing these are not included in transactions. See this blog on the excellent SQL Server Central for why this is.

UPDATE: Having reread my answer it strikes me that my opening sentence is a bit cheeky. The point I was trying to make, very badly, is it's more important to focus on the techniques provided here than the actual answer.

Upvotes: 2

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14077

  • Would this query work?
  • Yes.

But before doing that, make sure you create a backup in case crap happens.

Prepare a table definition for a backup:

SELECT TOP (0) *
INTO TemporaryBackupTable
FROM a;

And then run DELETE statement, which also will insert deleted records into prepared backup table.

DELETE a.*
OUTPUT DELETED.* INTO TemporaryBackupTable
FROM    A a
        JOIN    B b ON a.xxxId = b.xxxId
        JOIN    C c ON b.yyyId = c.yyyId
WHERE NOT EXISTS ( SELECT * FROM D d WHERE c.yyyId = d.yyyId);

Upvotes: 3

Related Questions