PostgreSQL – How to Delete Rows When Joining and Returning Data

deletejoin;postgresqlselect

I have two queries:

The first one is as below: 

SELECT * 
FROM batch bp 
  INNER JOIN sender_log sl ON sl.id=bp.log_id 
    AND bp.protocol='someprotocol'

The second query is used to delete rows: 

delete from batch where protocol='someprotocol'

I know that in postgres we have RETURNING * to return deleted rows. What I want to achieve is to delete rows and return them in one query. How can I accomplish this?

Best Answer

Very simple indeed, but you do need to include the other WHERE clause as well:

DELETE FROM batch bp
USING  sender_log sl
WHERE  bp.log_id = sl.id
AND    bp.protocol = 'someprotocol'
RETURNING bp.*, sl.*;

And to actually return what your question outlines, you need to include both tables in the RETURNING clause.

Related Question