Board index » delphi » Delete SQL

Delete SQL

In Delphi 2.0 C/S:
I have a local table that I wish to delete rows matching values in another local
table. I can build a query in Paradox that does it, but when I try and convert it to
SQL, I get all kinds of errors. Basically here is what I have:

DELETE
FROM Table1.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

When I do this, typically I get an error message that field 6 is invalid. Removing the
last SQL row results in the same error message for row 5 and so on.

I have tried the Visual Query Builder, Paradox, you name it. I would be grateful for
any assistance in this matter. I think a subSELECT would work, except Delphi does not
seem to accept them.

David Clark
sonoe...@ix.netcom.com

 

Re:Delete SQL


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


How about a BatchMove component, using the batDelete mode?

Clay Shannon

 __________________________________________________
B. Clay Kollenborn-Shannon
InfoHighway 101, P.O. Box 1116, San Andreas,CA 95249
Baseball software
ClaySie...@aol.com  

Other Threads