Search code examples
sqlselectsql-delete

SQL - return deleted rows

I would like to combine a SELECT which returns rows and a DELETE which deletes a subset of the rows I selected?

Is this possible?

Solution

  • If you have a SELECT statement that returns all the candidates, just change SELECT to DELETE with OUTPUT DELETED.*. 

     SELECT * 
 FROM tbl1
 INNER JOIN tbl2 on tlb1.col = tbl2.col
 INNER JOIN tlb3 on tbl2.anothercol = tbl3.somecol
 WHERE blah blah blah

    Can become:

     DELETE tbl1 OUTPUT DELETED.*
 FROM tbl1
 INNER JOIN tbl2 on tlb1.col = tbl2.col
 INNER JOIN tlb3 on tbl2.anothercol = tbl3.somecol
 WHERE blah blah blah