SQL Anywhere: getting error when trying to delete results of a subquery using a join

Question

I created the following script to find rows which had a depth +-2 feet from another row:

select a.*
from   WELL_FORMATION a,
       WELL_FORMATION b
where  a.UWI=b.UWI
and    a.FORM_ID=b.FORM_ID
and    a.SOURCE != 'ABCD'
and    b.SOURCE  = 'ABCD'
and    abs(a.GX_FORM_TOP_DEPTH - b.GX_FORM_TOP_DEPTH) <= 2

This script returns exactly what I need. Now I need to delete the results from this query.

Using the information found here, I came up with the following:

DELETE t1
from well_formation t1
inner join
(
    select a.*
    from   WELL_FORMATION a,
           WELL_FORMATION b
    where  a.UWI=b.UWI
    and    a.FORM_ID=b.FORM_ID
    and    a.SOURCE != 'ABCD'
    and    b.SOURCE  = 'ABCD'
    and    abs(a.GX_FORM_TOP_DEPTH - b.GX_FORM_TOP_DEPTH) <= 2
) t2 on t1.uwi=t2.uwi
     and t1.form_id=t2.form_id
     and t1.source=t2.source

However, I am getting an error message stating Sybase can't find a table called t1. When I remove "t1" from after DELETE, I get an error on the inner join.

I know I am getting the correct results because if I replace the "DELETE t1" with "SELECT *" I get the records that I want to delete.

I am using SQL Anywhere. Does anyone have any suggestions on what else I could try?

Thanks!

Answer 1

Apparently, from everyone I've spoken with, this is a particular quirk of SQL Anywhere. The statement as I had it works fine in Oracle and even in with Transact SQL, but it is not a part of the SQL Standard in SQL Anywhere.

However, someone was finally able to find something that would work. It requires using 2 FROM clauses.

Here is the final script:

delete 
from well_formation as t1
from well_formation as t2
where  t1.UWI=t2.UWI
and    t1.FORM_ID=t2.FORM_ID
and    t1.SOURCE != 'ABCD'
and    t2.SOURCE  = 'ABCD'
and    abs(t1.GX_FORM_TOP_DEPTH - t2.GX_FORM_TOP_DEPTH) <= 2;

Hopefully this helps anyone else that comes across this issue.