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?

What RDBMS? Also can you provide a sample showing the desired results? — Martin Smith, May 21 '13 at 12:12
SQL server. The results would be the same as doing a SELECT on some of the rows returned would no longer exist in the database — Geoff Scott, May 21 '13 at 12:35
Under MS SQL Server, you can use: `DELETE ... WHERE ... OUTPUT DELETED.*`. — Branko Dimitrijevic, May 21 '13 at 12:39

Bill Gregg · Accepted Answer · 2013-05-21T15:12:53.000

14

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

edited May 21 '13 at 15:12

answered May 21 '13 at 12:35

Bill Gregg

7,067
2
22
39

I get an Incorrect syntax near 'OUTPUT'. Am using SQL Server 2005 – Geoff Scott May 21 '13 at 13:53
1

It should work with SQL 2005. You might have to put it into a table variable, like this: DELETE FROM TestTable OUTPUT Deleted.ID, Deleted.TEXTVal INTO @TmpTable – Bill Gregg May 21 '13 at 14:12
Yes, I updated the reply but it gave me an awaiting peer review message. The INTO clause is optional. :-) – Geoff Scott May 21 '13 at 14:27
The syntax would be RETURNING * in psql. – Dan Jan 11 '17 at 08:57

score 2 · Answer 2 · answered May 04 '21 at 09:14

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

SQL - return deleted rows

2 Answers2