SQL question

[Dave Hull]

On Fri, 8 Aug 2003, Brian Densmore wrote:

> The query is :
> 
> use xtra_edi 
> delete msg_element 
> from msg_element inner join message 
>    on msg_element.indice = message.indice
>    inner join bad_marks on bad_marks.mark = message.mark
> where message.mark = bad_marks.mark
> 
> [not standard sql]
> { I might have been able to write it like this:
> use xtra_edi 
> delete from msg_element 
>  where msg_element.indice in
>   ( select message.indice from message
>    inner join bad_marks on bad_marks.mark = message.mark
> where message.mark = bad_marks.mark)
> }

So is this another way of expressing your query:

delete from
   msg_elements A
where
   A.indice in (
      select
         B.indice
      from
         messages B,
         bad_marks C
      where
         C.mark = "Bad mark value" and
         B.mark = C.mark
   )

And if so, this query runs more than a day?

Have you tried something like this:

select
   'delete from msg_elements A where A.indice = ''' || B.indice || '';'
from
   messages B,
   bad_marks C
where
   C.mark = "Bad mark value" and
   B.mark = C.mark

which will give you a long list of delete statements which you should be able 
to feed back into the machine. Splitting the job this way may be more (or 
less) efficient.

Sorry I'm not more help.

-- 
Dave Hull
http://insipid.com

Good salesmen and good repairmen will never go hungry.
		-- R.E. Schenk