0

I would like to return the ids which have been deleted by a DELETE query.

On Stackoverlow, I found this: How to get ID of the last updated row in MySQL?

The top1 answer has a very nice solution, but it's for mysql. I tried to do the same in Firebird after reading some part of the Firebird manual:

set term ^ ;

EXECUTE BLOCK
AS
    DECLARE VARIABLE uids BLOB SUB_TYPE TEXT;
begin
    DELETE FROM CATEGORY WHERE name = 'Haló'
    AND ( SELECT id INTO :uids );
    SELECT @uids;
end
^

Yes, I know that 'uids' will always contain one ID, since I'm overwriting the variable, but it's only a test, and what is more, it doesn't work. It stops at 'INTO' saying "Token unknown - line 8, column 21". I don't know what to do, what to continue with.. :\

Thanks for Your help!

Community
  • 1
  • 1

2 Answers2

2

For this, please run separate queries

  1. First fetch the record ids those you want to delete by the same where condition for DELETE

like, SELECT ID FROM CATEGORY WHERE name = 'Haló'

  1. Then delete the records
sudheshna
  • 1,150
  • 1
  • 13
  • 24
  • Thanks for the answer. So it is the only way. Anyway this was my first idea, but I thought it would be slower because of the SELECT query. –  Jul 06 '11 at 10:09
1

you could try the "RETURNING" clause, like this:

delete from Scholars
  where firstname = 'Henry' and lastname = 'Higgins'
  returning lastname, fullname, id

look here for more details

lordvlad
  • 5,200
  • 1
  • 24
  • 44