At 02:43 PM 4/03/2005 +0000, you wrote:

>After this query type the firebird engine seems to be in cycle.
>
>delete pr_lekrvzp t where exists (select 1 from pr_lekrvzp where
>t.n_part=n_part having count(*)>1 and t.poradi<>max(poradi))
>
>Utilization CPU on linux is about 99.5 %, mem usage <10 %. When I
>abnormally stop the client (console) utilization gets down to 0%.
>
>I have tried some other queries and when I have used EXISTS , IN or
>subquery always this ends similarly.
>
>Can you help me?

Several syntax errors here.

A DELETE query's syntax pattern is

-- delete from aTable where...

-- your subquery is trying to be a grouped query but doesn't include a
GROUP BY clause

-- you can't use the EXISTS (select 1 from.. ) syntax in a grouped subquery

-- a self-referencing correlated subquery has to use aliases to ensure that
the engine operates on separate cursors.

Change the syntax to the following:

delete FROM pr_lekrvzp t
where t.n_part is not null
and exists
(select T1.n_part from pr_lekrvzp T1
where T1.n_part = t.n_part
and t.poradi <> max(T1.poradi)
GROUP BY 1 )

/* having count(T1.*) > 1 is redundant (and expensive) because, if there
is any row where t.poradi <> max(T1.poradi), then the count logically MUST
be > 1.

However, this query does not guarantee to eliminate all rows where t.poradi
is reduplicated. If there are multiple rows where t.poradi has the same
value as max(T1.poradi), none of those rows will be selected. If your data
allows this possibility and your aim is eliminate the duplicates, a SP is
needed.
*/

./hb