Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 7 down vote accepted

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.

share|improve this answer

Very simple indeed:

DELETE FROM batch bp
USING sender_log sl
WHERE bp.log_id = sl.id
RETURNING sl.packet

;-)

share|improve this answer
    
What does RETUNRNING sl.packet do? – Mihai Apr 26 '14 at 13:39
1  
@Mihai: I added a link to my answer, the manual explains. – Erwin Brandstetter Apr 26 '14 at 17:33
    
@ErwinBrandstetter, your answer fulfills what I've asked. thanks. +1 – Alireza Hos Apr 27 '14 at 6:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.