SQL Server : using information from one table to delete rows in another

Question

I am having some difficulty writing some SQL Server script to remove a few rows from two databases. I have seen quite a few other questions on the topic but nothing that appears to work for this scenario. I apologize in advance for such a basic question but this is a query which a cannot afford to screw up. I query a lot of data but I seldom make writes to tables.

I have two tables rollinfo and defects which are linked by

defects.roll_id = rollinfo.roll_idx

There is a column in the rollinfo table called rollinfo.num_defects.

If that value is greater than @MAX_DEFECTS, I want to delete it. In addition I want to remove any corresponding rows from the defects table.

Selecting this information is easy:

SELECT
    D.ROLL_ID,
    R.ROLL_IDX
FROM 
    VISION17SLITTER.DBO.ROLLINFO R
INNER JOIN 
    VISION17SLITTER.DBO.DEFECTS D ON D.ROLL_ID = R.ROLL_IDX
WHERE
    R.NUM_DEFECTS > @MAX_DEFECTS

However I wasn't sure if rows could be deleted from two tables at once by just changing the select statement to delete or if I need to do some type of "where exists" statement.

For the record, this will be about 200k rows, so if there is more than one way to do this, I would like to know which is more efficient.

Thanks,

Dan

Use can use a `cascading delete` foreign key constraint, so you only have to delete the defects once and they will be removed from related tables. — Gordon Linoff, Oct 10 '16 at 18:07
Possible duplicate of [delete from two tables in one query](http://stackoverflow.com/questions/1233451/delete-from-two-tables-in-one-query) — pid, Oct 11 '16 at 08:18

marc_s · Accepted Answer · 2016-10-11T04:40:14.420

Since you need to delete from two tables, you will need to have two DELETE statements, so I would think the best approach would be to write those ID's you need to delete into a temporary table, and then use that to drive your two DELETE statements:

-- create the temp table - you didn't mention what the datatype for these two columns is - adapt as needed
CREATE TABLE #IDsToBeDeleted (ID INT NOT NULL)  

-- select those ID's you want to delete into the temp table
INSERT INTO #IDsToBeDeleted (ID)  
    SELECT
        D.ROLL_ID
    FROM 
        VISION17SLITTER.DBO.ROLLINFO R
    INNER JOIN 
        VISION17SLITTER.DBO.DEFECTS D ON D.ROLL_ID = R.ROLL_IDX
    WHERE
        R.NUM_DEFECTS > @MAX_DEFECTS

-- based on the temp table, now delete from the two tables  
-- again, from your question it isn't entirely clear which 
-- is the "parent" table, and which the "child" table - so you
-- might need to change the order of deleting those rows to
-- match your situation

BEGIN TRANSACTION
BEGIN TRY    
    DELETE FROM VISION17SLITTER.DBO.DEFECTS 
    WHERE ROLL_ID IN (SELECT ID FROM #IDsToBeDeleted)    

    DELETE FROM VISION17SLITTER.DBO.ROLLINFO 
    WHERE ROLL_IDX IN (SELECT ID FROM #IDsToBeDeleted)

    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    -- report and log the error
    ROLLBACK TRANSACTION
END CATCH

marc_s, That makes a lot of sense I figured I would need to delete statements. I'll give this a try. — mreff555, Oct 10 '16 at 18:19
I would suggest wrapping that in a transaction to in case an issue occurs after your first table has deleted while deleting from ROLLINFO, otherwise you might not be able to re-create the id list and re-run the delete. — Edward Comeau, Oct 10 '16 at 21:35
@EdwardComeau: absolutely - updated my response to highlight that fact — marc_s, Oct 11 '16 at 04:39

pid · Answer 2 · 2016-10-11T08:29:35.193

Have you tried this?

This may or may not work depending on your DB.

DELETE D, R
FROM
    VISION17SLITTER.DBO.ROLLINFO R
    INNER JOIN
    VISION17SLITTER.DBO.DEFECTS D
    ON D.ROLL_ID = R.ROLL_IDX
WHERE R.NUM_DEFECTS > @MAX_DEFECTS;

Otherwise, use two deletes in the same batch (you say you want to write a script, so a batch is the correct thing to do). In this simple case you don't need a transaction because you already know that you're the only person interacting with the DB:

DELETE D
FROM
    VISION17SLITTER.DBO.ROLLINFO R
    INNER JOIN
    VISION17SLITTER.DBO.DEFECTS D
    ON D.ROLL_ID = R.ROLL_IDX
WHERE R.NUM_DEFECTS > @MAX_DEFECTS;

DELETE R
FROM
    VISION17SLITTER.DBO.ROLLINFO R
WHERE R.NUM_DEFECTS > @MAX_DEFECTS;

GO

If you don't really want a script to launch manually while the application stands still, you must consider concurrency and an actual transaction is in order:

BEGIN TRANSACTION

  DELETE D
  FROM
    VISION17SLITTER.DBO.ROLLINFO R
    INNER JOIN
    VISION17SLITTER.DBO.DEFECTS D
    ON D.ROLL_ID = R.ROLL_IDX
  WHERE R.NUM_DEFECTS > @MAX_DEFECTS;

  DELETE R
  FROM
    VISION17SLITTER.DBO.ROLLINFO R
  WHERE R.NUM_DEFECTS > @MAX_DEFECTS;

COMMIT;

You can't delete from two tables with one `DELETE` statement. — Vladimir Baranov, Oct 11 '16 at 04:53
untrue, it depends on the DB implementations, there are some oracle and mysql (innodb) versions which support it, that's why I present both solutions, if the first doesn't work (which the OP has to test) then the second comes in. — pid, Oct 11 '16 at 08:23
well, the question is tagged [sql-server], which definitely doesn't support such syntax. On the other hand, I didn't realize that other DBMS may support it. I learned something new today. Thanks. — Vladimir Baranov, Oct 11 '16 at 12:51

SQL Server : using information from one table to delete rows in another

2 Answers2