From: Albert Steckenborn (a.steckenbornstebosoft.de)
Date: Mon Jul 19 2004 - 14:28:37 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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 <a.steckenbornstebosoft.de>
> 07/19/2004 02:51 PM
>
>
> To: maxdblists.mysql.com
> 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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]