Re: filter for DELETE

On 30 Sep 2005 01:36:49 -0400, Joseph wrote:

>Hi,
>I don’t know to write SQL command filter.
>m_strQueryDelete.Format("DELETE FROM tab WHERE (Col1 = ’abc’ AND Col2
>= ’abc’ AND ?????)"
>example: ODBC found 100 records.
>I need to delete first 90 records and to leave last 10 records.
>
>Thank you
>
>Joseph

Hi Joseph,

Define: "first 90". You see, tables in the relational model are, by
definition, UNordered collections of data. There is no such thing as
"first" or "last" until you define an ordering.

For example, if you have a column EntryDate in your table and you want
to delete the 90 oldest rows that have 'abc' in both Col1 and Col2, you
would use:

DELETE FROM tab
WHERE  Col1 = 'abc'
AND    Col2 = 'abc'
AND   (SELECT COUNT(*)
FROM   tab AS t2
WHERE  t2.Col1 = 'abc'
AND    t2.Col2 = 'abc'
AND    t2.EntryDate <= tab.EntryDate) <= 90


Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)