How to delete all records from a dataset selection ?

[gelinp]

Hi,

I know method Delete from Dataset to delete current record in a dataset. But if I want to delete all records from a SELECT * FROM <table> WHERE <condition> statment, how do you do ?

I doesn't want to iterate with Locate method, I would like to call only one Delete method from the dataset wich select stament was executed... May be I need to execute specific SQL stament ? Like DELETE FROM <table> WHERE <condition>, with a call to ExecuteDirect method from my database connection ?

Thank you for your answer.
Patrick

[marsupilami]

Hello Patrick,

SQL is your answer. Issue a "delete from <table> WHERE <condition>". Zeos is not made for this and to me it seems that it doesn't make sense to delete 100 records by their primary keys, each with a single statement. Also remember that this would require that many round trips to the database. Doing your select and your delete in one single transaction might be key here if you are afraid of loosing data that was not showed in your dataset.

If issuing a "delete from <table> WHERE <condition>" is not right for you, you might want to look into something like execute block where you can group multiple statements in one SQL call. something like

execute block begin
delete from <table> where >primarykey> = <primarykeyvalue_1>;
...
delete from <table> where >primarykey> = <primarykeyvalue_n>;
end;

Best regards,

Jan