Hi Holger,
no i have done the same with a cursor and it is working.
declare C cursor for
select a.id from bas_agnums a where a.id in(select b.id from bas_agnums b, va_ums c where b.id=c.ag_id(+) and c.ag_id is null) for reuse
//
delete from bas_agnums a where a.id in(select id from C)
Now there are only 80000 rows in table A and that is right.
It feels as if the use of the "id" at both select statements ("... where id in (select id ....")is creating the problem.
So the cursor is a good workaround.
rgds.
Albert
Albert Steckenborn schrieb:
Hi folks,
following situation:
KERNEL 7.5.0 BUILD 015-121-074-675 updated yesterday from 008 because join problems. SuSE Linux 8.2, P4 2600, 1GB RAM, 2 Ultra160 raw devices.
I want to delete all rows from table A (150.000 rows) without a reference in table B (380.000 rows).
At first i have done a select to make shure for creating the right result.
statement: bas_agnums.id=Primary key
select id from bas_agnums where id in(select distinct id from bas_agnums b left join va_ums on id=ag_id where ag_id is null)
result is ok with 55865 rows. Time for request about 13sec.
where starting the following delete
delete from bas_agnums where id in(select distinct id from bas_agnums b left join va_ums on id=ag_id where ag_id is null)
No rows updated or deleted. No Result. Thats definitive a bug.
When trying to do the same with "exists" or "all" clause request needs more than 4 hours and the database is no more responding for other request to table bas_agnums. Thats not acceptable for my customers.
with best rgds.
Albert Steckenborn
-- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
