SQL - return deleted rows

Question

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?

Answer 1

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

Answer 2

I'm using pgAdmin 3 and for some reason the syntax in the accepted answer didn't work for me (not sure why, the error was: ERROR: syntax error at or near "DELETED")

What worked for me was:

DELETE 
 FROM tbl1
 where column1 = value
 RETURNING *;

For more information see the docs