Is EXISTS clause supported by DBISAM's sql engine?

Question

enter image description here Strangely, it seems that the EXISTS clause is not supported by DBISAM's sql engine, as it's always resulting in an SQL error. The following is a sample where EXISTS is being used. Am i missing anything here?

update Table1 set HASXACTION = False
WHERE EXISTS (SELECT SERIALID
              From Table2  
              LEFT JOIN Table1 ON (Table2 .AUXILACT = Table1 .CODE) 
                               AND (Table2 .CHARTACT = Table1 .CHARTACT) )

What is the error? For instance, this syntax wouldn't be allowed on MySQL. — Gordon Linoff, Nov 26 '14 at 12:39
DBISAM supports the EXISTS statement, so the problem lies in your query... — whosrdaddy, Nov 26 '14 at 12:43
i posted a screenshot of the error i'm getting using the database utility tool of DBISAM itself. — Johny, Nov 26 '14 at 13:14
@whosrdaddy, note that the same SQL statement works fine with the sql engine of BDE for instance. — Johny, Nov 26 '14 at 13:26

Gordon Linoff · Answer 1 · 2014-11-26T16:49:06.260

2

I presume that you don't really want the join in the subquery. You probably intend a correlated subquery:

UPDATE Table1
    SET HASXACTION = False
    WHERE EXISTS (SELECT SERIALID
                  FROM Table2  
                  WHERE Table2.AUXILACT = Table1.CODE AND Table2.CHARTACT = Table1.CHARTACT
                 );

This should also fix the problem you are having, which is the reference to Table1 both in the update clause and the subquery. (This is a MySQL limitation.)

EDIT:

I cannot find any reference to EXISTS (or even subqueries) for dbisam. But, you can do updates with joins, so this should be equivalent:

UPDATE Table1
    SET HASXACTION = False
    FROM Table1 JOIN
         Table2
         ON Table2.AUXILACT = Table1.CODE AND Table2.CHARTACT = Table1.CHARTACT;

edited Nov 26 '14 at 16:49

answered Nov 26 '14 at 12:41

Gordon Linoff

1,242,037
58
646
786

While ElevateDB supports correlated subqueries, DBISAM does not. – Chris Thornton Nov 26 '14 at 12:56
Um, MySQL? DBIsam is a proprietary DB engine from Elevate Software, I didn't think there was any connection with MySQL. – MartynA Nov 26 '14 at 13:12

score 2 · Accepted Answer · answered Nov 26 '14 at 13:48

2

Never mind people, i just learned that DBISAM does not support EXISTS operator for specifying sub-select predicates in WHERE clauses. It's documented within DBISAM's help file(screenshot attached).

DBISAM Help Documentation

answered Nov 26 '14 at 13:48

Johny

419
4
15

Well, that perfectly answers my question as well,. i was looking for a faster way to do distinct,. but seems that's not going to fly. – T.S Jul 05 '19 at 09:16

score 1 · Answer 3 · answered Nov 26 '14 at 13:59

As you've already found, you can do it with the IN. However, there is a limitation that IN can only work with one field. So you can get around it by concatenating two fields to make expressions that fit the criteria. One for the inner, and one for the outer.

update Table1 set HASXACTION = False
WHERE Code+'.'+CHARTACT IN 
 (  
   SELECT  AUXILACT+'.'+CHARTACT From Table2
 )

Is EXISTS clause supported by DBISAM's sql engine?

3 Answers3