Hi Noah,
that was my first idea. But is not working.
You can not update a join in MAXDB (Message:Invalid end of SQL Statement).
I've seen a post by Elke Zabach with a hint to use "exists clause".
I've tried to set a flag with this (NO_UMS='N') to delete the rows without the flag.
Thats not working in my case. Query is working (SQL-Studio) for more than 1 our and hangs.
The count with "in" clause comes up in 10 seconds.
Datebase Server SuSE-Linux 8.2, P4, 2600Mhz, 1GB RAM, 3 ultra160 raw devices.
with best regards
Albert
Noah J SILVA schrieb:
Hi Albert,
I haven't run into this, and it does look like a problem, but I can suggest a work-around. (I use ms-sql-server here for some databases, so I know all about work-arounds!).
I've seen all sorts of problems with the "IN" keyword on different databases. Perhaps it's more difficult to implement than I immagine?
you could:
a.) Select those rows (just the keys) into a temporary table and then use that to delete the rows.
or
b.) Add a tag column to the table, and set the tag on the rows you want deleted. You can then just delete the rows with the tag.
or
c.) Rewrite the query to avoid the "IN" keyword. The easiest way (for me) is to use an outer-join and look at one of the columns that will be filled in my nulls where there are no rows in the other table for your delete condition. In this case, since you are using IN to look at the same table where you are pulling the ID from in the select, I am not sure why you need it at all.
Why not:
DELETE from bas_agnums b,
va_ums c
where b.id=c.ag_id(+) and c.ag_id is null
This is just upon a quick glance, so perhaps I am missing something!
Thank you, Noah Silva Atofina IS&T - Sr. Programmer Analyst (215) 419 - 7916
Albert Steckenborn <[EMAIL PROTECTED]> 07/19/2004 02:51 PM
To: [EMAIL PROTECTED]
cc: Subject: Problem with delete / update statement and in clause (with join)
hi folks,
following problem with actual maxdb release:
I have found no way to delete rows from table A without a reference in table B.
Table A 138000 rows
Table B 380000 rows
select count(ID) from bas_agnums where id in(select distinct id from bas_agnums,va_ums where bas_agnums.id=va_ums.ag_id(+) and va_ums.ag_id is null)
result: 55865
That is ok
Now i want to delete these rows with following statement:
delete from bas_agnums where id in(select distinct id from bas_agnums,va_ums where bas_agnums.id=va_ums.ag_id(+) and va_ums.ag_id is null)
result: No rows updated or deleted. No Result
That is wrong
Have tried a lot of ways and found nothing that is working.
Any hints?????
with best rgds.
Albert
-- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
