Re: One DELETE with OR clause behaves differently from two DELETEs ?

Brian Peasland wrote:
> > DELETE FROM MACHINE_DOWN_TIMES
> > WHERE STOP_MEASURE_ID IN (
> > SELECT ID FROM MEASURES WHERE ORDER_ID = :p_order_id AND TIME_STAMP >
> > :p_order_end_date
> > )
> > OR STOP_END_MEASURE_ID IN (
> > SELECT ID FROM MEASURES WHERE ORDER_ID = :p_order_id AND TIME_STAMP >
> > :p_order_end_date
> > );
>
> Why not write the above as follows:
>
> DELETE FROM MACHINE_DOWN_TIMES
> WHERE STOP_MEASURE_ID IN (
> SELECT ID FROM MEASURES
> WHERE (ORDER_ID = :p_order_id AND TIME_STAMP > :p_order_end_date)
> OR (ORDER_ID = :p_order_id2 AND TIME_STAMP > :p_order_end_date2)
>
> HTH,
> Brian
>

Brian - thanks for your reply.

I think you may have misread the query?

I have these tables (abbr.):
MACHINE_DOWN_TIMES =
STOP_MEAS_ID(foreign key MEASURES.ID) / STOP_END_MEAS_ID(foreign key MEASURES.ID) / DT_DATA MEASURES=
ID / TIME_STAMP / ORDER_ID / M_DATA Now I have a certain set in measures defined by: SELECT ID FROM MEASURES WHERE ORDER_ID=:p_order_id AND TIME_STAMP>:p_order_end_date

And I want to delete all machine_down_time entries were such an ID is either in STOP_MEAS_ID or in STOP_END_MEAS_ID

I would have thought the two ways I posted in my 1st message to be sematically the same - but ORACLE behaves very differently.

best,
Martin Received on Fri Sep 01 2006 - 09:22:47 CDT