G'day folks,
I want to delete certain records from multiple linked tables.
The criteria (defined in sFILTER) is e.g. ClientType = 2
What I did is, I created the following SQL and delete records in a loop.
The thing is, that the loop seems to work quite well, it counts down
(indicated by the recordCount value, but:
- it deletes not all the records with the specified criteria, and
- it also deletes a few records outside of the specified criteria,
- sometimes it runs through the loop and after all it did not delete any
records at all.
What's wrong?
Please find the code below:
disableControls;
query.clear;
query.add('SELECT c.ID, c.ClientType, p.ID');
query.add('FROM RE_Client c, RE_Property p');
query.add('WHERE (c.ID = p.ID) and (c.' + sFilter + ')');
OpenAllTables;
while recordCount > 0 do
begin
try tblClientMaster .delete except
on e:exception do
begin
showMessage('Failed deleting!' + cCR + e.message);
exit;
end;
end;
lbl_City.caption := intToStr(recordCount); // show progress
application.processMessages;
end;
Note that the other tables are linked up to the ClientMaster table with the
table properties MasterSource and MasterFields
Is this the right way to do a delete on multiple tables?
Can I do this in one go with the SQL "DELETE" statement, if so HOW do I
specify the other tables?
Thanks a lot for any help.
John.
winmail.dat
