Board index » delphi » delete SQL - please help

delete SQL - please help

I have a local Paradox table ("A")that I would like to delete all
records that exist in another local Paradox table ("B").  I can build a
delete query in Paradox that works great.  I tried to convert into SQL
and of course it would not convert it for me.  So I wrote the following
SQL in a TQuery which should be the same:

DELETE
FROM Table1.db, Table2.db
WHERE
Table1.db.Field1 = Table2.db.Field1 AND
Table1.db.Field2 = Table2.db.Field2 AND
Table1.db.Field3 = Table2.db.Field3 AND
Table1.db.Field4 = Table2.db.Field4 AND
Table1.db.Field5 = Table2.db.Field5 AND
Table1.db.Field6 = Table2.db.Field6

However this generates an Token error message about the comma in the
FROM statement.

I am going under the impression that this should be exactly like a
SELECT but it deletes instead.  So I plan to call it with
TQuery1.ExecSQL. or TQuery1.open.

Am I doing this wrong?  Should I be using the TUpdateSQL?  The tables
are not cached or read only.  Nothing dramatic, although Table A is a
child table with Referential Integrity.  But since I am deleting from
the child first there should be no conflict.

I would greatly appreciate any help you can send my way.

Frustrated...

David
sonoe...@ix.netcom.com
davi...@exabyte.com

 

Re:delete SQL - please help


Quote
David Clark <davi...@exabyte.com> wrote:
>I have a local Paradox table ("A")that I would like to delete all
>records that exist in another local Paradox table ("B").  I can build a
>delete query in Paradox that works great.  I tried to convert into SQL
>and of course it would not convert it for me.  So I wrote the following
>SQL in a TQuery which should be the same:
>DELETE
>FROM Table1.db, Table2.db
>WHERE
>Table1.db.Field1 = Table2.db.Field1 AND
>Table1.db.Field2 = Table2.db.Field2 AND
>Table1.db.Field3 = Table2.db.Field3 AND
>Table1.db.Field4 = Table2.db.Field4 AND
>Table1.db.Field5 = Table2.db.Field5 AND
>Table1.db.Field6 = Table2.db.Field6

Try
 DELETE
 FROM Table1.db
 WHERE EXIST
   (SELECT * FROM Table2.db
    WHERE
       Table1.db.Field1 = Table2.db.Field1 AND
       Table1.db.Field2 = Table2.db.Field2 AND
       Table1.db.Field3 = Table2.db.Field3 AND
       Table1.db.Field4 = Table2.db.Field4 AND
       Table1.db.Field5 = Table2.db.Field5 AND
       Table1.db.Field6 = Table2.db.Field6)

Quote
>However this generates an Token error message about the comma in the
>FROM statement.

You can DELETE only from one table at a time.

Quote
>I am going under the impression that this should be exactly like a
>SELECT but it deletes instead.  So I plan to call it with
>TQuery1.ExecSQL. or TQuery1.open.

You can use only ExecSQL for DELETE, UPDATE, INSERT.

Quote
>Am I doing this wrong?  Should I be using the TUpdateSQL?  The tables
>are not cached or read only.  Nothing dramatic, although Table A is a
>child table with Referential Integrity.  But since I am deleting from
>the child first there should be no conflict.
>I would greatly appreciate any help you can send my way.
>Frustrated...
>David
>sonoe...@ix.netcom.com
>davi...@exabyte.com

Hope this will help.

Vadim.

Re:delete SQL - please help


Thank you for all your help - I got it to work.  Was just a syntax
problem on my subquery.

David

Other Threads