SQL - returns deleted rows

Question

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

Is it possible?

Answer 1

If you have a SELECT statement that returns all candidates, just change SELECT to DELETE using 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 be:

  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