| Subject | Re: [firebird-support] delete min |
|---|---|
| Author | Helen Borrie |
| Post date | 2008-03-19T21:05:16Z |
At 07:12 AM 20/03/2008, you wrote:
IMO, a PSQL treatment is the right way to do this, if it must be done. If using Fb 2 or higher, then you could use EXECUTE BLOCK; otherwise write a stored procedure.
Like others, I have doubts that deleting patients' detail records is a safe way to keep the records tidy, in any case. There will be "rules" in the requirements to determine how to deal with history and deletion is not likely to be an option. In every medical records system I have ever encountered, there is an overriding rule, with a basis in Law, that you *never* delete anything in the records with respect to patients, not even entries in the Appointments diary. So I'd want to design such a system so that records could be disabled but never deleted....
If there is a rule that says "records can be deleted" then implement the rule as a field in the table, e.g., a date field to apply a "lifetime" rule, a counter field to apply a "number of events" rule or a Status field to apply a "status" rule.
./heLen
>Alejandro Garcia wrote:I would expect it to work, but too well! There's nothing here to prevent this statement from continuing to cycle through until it has deleted all of the records in the table (except those where NUMPAT or "DATE" were NULL). At the very least I would want to test this theory on a copy of the database!
>
>> There are many dates for each patient and I need to select the minimun
>> date for each patient. This SQL works fine:
>>
>> SELECT T.NUMPAT,T.TREATMENT,T.DATE
>> FROM MYTABLE T
>> WHERE
>> T.DATE=
>> (SELECT MIN(Q.DATE) FROM
>> MYTABLE Q WHERE T.NUMPAT = Q.NUMPAT )
>> order by T.NUMPAT
>>
>> By now I need to delete that minimun date for each patient, and simply
>> change the first SELECT for a DELETE is not working... how could I?
>> Sorry for this so simple question...
>
>I would have expected this to work:
>DELETE
>FROM MYTABLE T
>WHERE
>T.DATE=
>(SELECT MIN(Q.DATE) FROM
>MYTABLE Q WHERE T.NUMPAT = Q.NUMPAT )
>
>Note that the order by has been removed.
>
>Why doesn't it work? What error do you get?
IMO, a PSQL treatment is the right way to do this, if it must be done. If using Fb 2 or higher, then you could use EXECUTE BLOCK; otherwise write a stored procedure.
Like others, I have doubts that deleting patients' detail records is a safe way to keep the records tidy, in any case. There will be "rules" in the requirements to determine how to deal with history and deletion is not likely to be an option. In every medical records system I have ever encountered, there is an overriding rule, with a basis in Law, that you *never* delete anything in the records with respect to patients, not even entries in the Appointments diary. So I'd want to design such a system so that records could be disabled but never deleted....
If there is a rule that says "records can be deleted" then implement the rule as a field in the table, e.g., a date field to apply a "lifetime" rule, a counter field to apply a "number of events" rule or a Status field to apply a "status" rule.
./heLen