DBISAM Statement : delete from table1 a left outer join table2 b on (b.idx1 = a.idx1) and
(b.idx2 = a.idx2) where b.idx3 = null

Can someone translate that into EDB SQL for me ? Shame on me, but I don't know what
"correlated sub-queries in the WHERE clause" are...

TIA,

Heiko

Heiko,

> delete from table1 a left outer join table2 b on (b.idx1 = a.idx1) and
> (b.idx2 = a.idx2) where b.idx3 = null

Untested:

delete from table1 where idx1 in (select idx1 from table2  where idx3 is
null) and idx2 in (select idx2 from table2 where idx3 is null)

Uli

Uli

thanks, but...won't work. Example :

table1:
idx1, idx2
1, 1
1, 2
1, 3

table2:
idx1, idx2, idx3
1, 1, 1
1, 2, 1

After execution of the DBISAM query the last row of table1 would be deleted.

Heiko

Heiko


delete from table1 where
(select idx3 from table2 where
table1.idx2 = table2.idx2
and
table1.idx1 = table2.idx1
) is null

Roy Lambert [Team Elevate]

Roy

Thanks !! That's it. I will need a while, until I get accustomed to that...

Heiko

Heiko


As a bit of a PS I almost always try and create a SELECT statement before doing a DELETE. Its usually much easier to see if you got it right, and for some reason I find it easier to develop the WHERE clause for a SELECT. Once it is working its just a matter of replacing SELECT * with DELETE.

Roy Lambert [Team Elevate]