Hello,

I hope someone can help me or maybe I'm missing something?

I have 2 tables Weights And DuplicateWeights.
I want to delete only the weights from table Weights which exist in table DuplicateWeights.

The criteria needs to be ID, Date, Weight.

I appreciate any guidance and I am using DBisam Version 2.05.

Thank you,

Thomas

Thomas <osmethod@osmethod.net> wrote on Sun, 14 May 2006 21:56:09 -0400

>Hello,
>
>I hope someone can help me or maybe I'm missing something?
>
>I have 2 tables Weights And DuplicateWeights.
>I want to delete only the weights from table Weights which exist in table DuplicateWeights.
>
>The criteria needs to be ID, Date, Weight.
>
>I appreciate any guidance and I am using DBisam Version 2.05.
>
>Thank you,
>
>Thomas
>
Thomas


Try something like:-

DELETE FROM Weights W
JOIN DuplicateWeights D ON (D.ID = W.ID AND D.Date = W.Date AND D.Weight = W.Weight)

HTH

Jeff


P.S.  I would first do this SELECT to make sure it gets the right records!  J.

SELECT W.* FROM Weights W
JOIN DuplicateWeights D ON (D.ID = W.ID AND D.Date = W.Date AND D.Weight = W.Weight)


--
Jeff Cook
Aspect Systems Ltd
Phone: +64-9-424 5388
Skype: jeffcooknz
www.aspect.co.nz

Jeff Cook <jeffc@aspect.co.nz> wrote:

Thomas <osmethod@osmethod.net> wrote on Sun, 14 May 2006 21:56:09 -0400

>Hello,
>
>I hope someone can help me or maybe I'm missing something?
>
>I have 2 tables Weights And DuplicateWeights.
>I want to delete only the weights from table Weights which exist in table DuplicateWeights.
>
>The criteria needs to be ID, Date, Weight.
>
>I appreciate any guidance and I am using DBisam Version 2.05.
>
>Thank you,
>
>Thomas
>
Thomas


Try something like:-

DELETE FROM Weights W
JOIN DuplicateWeights D ON (D.ID = W.ID AND D.Date = W.Date AND D.Weight = W.Weight)

HTH

Jeff


P.S.  I would first do this SELECT to make sure it gets the right records!  J.

SELECT W.* FROM Weights W
JOIN DuplicateWeights D ON (D.ID = W.ID AND D.Date = W.Date AND D.Weight = W.Weight)


--
Jeff Cook
Aspect Systems Ltd
Phone: +64-9-424 5388
Skype: jeffcooknz
www.aspect.co.nz



Hi Jeff,

Thank you for your reply.
Your select works fine for finding the duplicate records however, DBisam 2.05 seems to complain about your "Delete" sytax. This is the problem I've been having.
The error is : DBisam Engine Error Number 11949 SQL error - End of DELETE statement expected, instead found JOIN

Any further help I will appreciate.

Thank you,

Thomas

Thomas <osmethod@osmethod.net> wrote:

Jeff Cook <jeffc@aspect.co.nz> wrote:

Thomas <osmethod@osmethod.net> wrote on Sun, 14 May 2006 21:56:09 -0400

>Hello,
>
>I hope someone can help me or maybe I'm missing something?
>
>I have 2 tables Weights And DuplicateWeights.
>I want to delete only the weights from table Weights which exist in table DuplicateWeights.
>
>The criteria needs to be ID, Date, Weight.
>
>I appreciate any guidance and I am using DBisam Version 2.05.
>
>Thank you,
>
>Thomas
>
Thomas


Try something like:-

DELETE FROM Weights W
JOIN DuplicateWeights D ON (D.ID = W.ID AND D.Date = W.Date AND D.Weight = W.Weight)

HTH

Jeff


P.S.  I would first do this SELECT to make sure it gets the right records!  J.

SELECT W.* FROM Weights W
JOIN DuplicateWeights D ON (D.ID = W.ID AND D.Date = W.Date AND D.Weight = W.Weight)


--
Jeff Cook
Aspect Systems Ltd
Phone: +64-9-424 5388
Skype: jeffcooknz
www.aspect.co.nz



Hi Jeff,

Thank you for your reply.
Your select works fine for finding the duplicate records however, DBisam 2.05 seems to complain about your "Delete" sytax. This is the problem I've been having.
The error is : DBisam Engine Error Number 11949 SQL error - End of DELETE statement expected, instead found JOIN

Any further help I will appreciate.

Thank you,

Thomas



I'm OK Now. I worked it another way.
I basically did a select count on all the weights and save this into a table. I then deleted all the weights from the main weights table. Then I imported
the "Counted" entries back into the Main Weights Table.

I'd still be interested to know why the join in the delete gives an error?

Thank you,

Thomas

Thomas,

<< Thank you for your reply. Your select works fine for finding the
duplicate records however, DBisam 2.05 seems to complain about your "Delete"
sytax. This is the problem I've been having.The error is : DBisam Engine
Error Number 11949 SQL error - End of DELETE statement expected, instead
found JOIN >>

DBISAM 2.x doesn't support joins in the DELETE statement, so you'll have to
use process the SELECT result set manually and do the deletions
navigationally with a while not eof..delete loop.

--
Tim Young
Elevate Software
www.elevatesoft.com

The subliminal suggestion here is *upgrade* ... believe me its worth it!

Adam

adam <adam@nospamplease.fmfoods.co.uk> wrote:

The subliminal suggestion here is *upgrade* ... believe me its worth it!

Adam

Thank you Tim and Adam.
The program that I use DBISam to interface with doesnt work for the upgraded versins. The design of the progrm is out of my control.
Would anyone know where I might find some examples of the type of scripts (loop, conditional etc) Tim has referred to?

Thanks again,

Thomas

Thomas,

<< The program that I use DBISam to interface with doesnt work for the
upgraded versins. The design of the progrm is out of my control.
Would anyone know where I might find some examples of the type of scripts
(loop, conditional etc) Tim has referred to? >>

It's not an SQL script - it has to be native Delphi code that uses the
DBISAM components directly.  Are you using Delphi with DBISAM or are you
just using the DBISAM DBSYS utility to run SQL statements ?

--
Tim Young
Elevate Software
www.elevatesoft.com