[libsql] Advice on SQL DELETEs inside a SELECT result loop

[Andy C. <and...@bt...>]

Hi,

I've come across what I expect is operator error on my part, so I was
hoping for some quick advice as to the direction I should be going. The SQL task I'm performing I suspect can
be re-written as a compound SQL statement, but while I learn SQL fully, I'm performing it in a couple of stages in Delphi.
This is where I have a query.....

If I do a "SELECT track_id FROM main WHERE checked='N'" and store the resulting list of numbers in an array or list, then
perform the delete command "DELETE FROM main WHERE track_id=%d", using this list of numbers, this works fine with no problems.

But if I try to write it all in one loop, it always fails to perform the DELETE commands.
No errors reported and it still runs down the full list of results inside the loop. 
Both ways below fail in the same way..... (maybe the odd typo as I'm writing partially from memory)



Using one DB object, but 2 result sets
--------------------------------------

rs1 := TResultSet.Create (Ldb);
rs2 := TResultSet.Create (Ldb);
Ldb.StartTransaction;
rs1.FormatExecute('SELECT track_id FROM main WHERE checked=%s',['N']);
while rs1.FetchRow do begin
   rs2.FormatQuery('DELETE FROM mp3_stats WHERE stats_id=%d',[temp]);
   rs2.FormatQuery('DELETE FROM main WHERE track_id=%d',[temp]);
end;
Ldb.Commit;
rs2.free;
rs1.free;




Using two DB objects 
--------------------
Ldb2.StartTransaction;
handle:=Ldb.FormatExecute('SELECT track_id FROM main WHERE checked=%s',['N']);
while Ldb.FetchRow(handle,row) do begin
   temp:=row['track_id'];
   Ldb2.FormatQuery('DELETE FROM mp3_stats WHERE stats_id=%d',[temp]);
   Ldb2.FormatQuery('DELETE FROM main WHERE track_id=%d',[temp]);
end;
Ldb.FreeResult(handle);
Ldb2.Commit;




Who'd like be first in line to call me a lemon ;-)
Thanks all.