Hi folks,
it seems as if the bug is related in the reference from "id in(select id".
I've have use a construction with "not in" at other tables that is working without any problems.
right way:
delete from bas_orgmem where cnt_id not in(select a.id from bas_org a, bas_orgmem b where a.id=b.cnt_id)
wrong way:
delete from bas_orgmem where cnt_id in(select cnt_id from bas_orgmem a, bas_org b where b.id(+)=a.cnt_id and b.id is null)
That means you cannot use the same key used for the "in clause" for your subquery.
In that case you have to use not in.
with best regards
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]
