At 09:55 AM 16-01-02 -0600, you wrote:

>Hi, I've been trying to make a DELETE statement with no success, I'm moving
>from DAO / BDE to IBO. D6 & IB6
>
>How can I do a DELETE with an INNER JOIN in IBO?
>
>I need to delete from one table
>Here's what I have:
>
>DELETE
>FROM Mov_CxP
>INNER JOIN ConceptB ON (Mov_CxP.Mov_Type = ConceptB.Mov_Type)
>WHERE ( ( ConceptB.Catalog = 'CXP' ) AND ( Mov_CxP.XDate > :DIni ) );
>
>It's supposed to delete from table MOV_CXP
>

SQL questions should go to the ib-support list...but...

Try

DELETE
FROM Mov_CxP M
WHERE XDate > :DIni
AND (Mov_Type IN (SELECT Mov_Type from ConceptB C
WHERE Catalog = 'CXP'
AND C.Mov_Type = M.Mov_Type))

This will be very slow on a large table. A stored procedure would be much faster:

create procedure delete_em (:Dini DATE)
as
declare variable :PrimaryKey integer ; /* whatever the primary key is */

BEGIN
for select M.ThePrimaryKey from Mov_CxP M
JOIN ConceptB C ON M.Mov_Type = C.Mov_Type
WHERE ( ( C.Catalog = 'CXP' ) AND ( M.XDate > :DIni )
INTO :PrimaryKey DO
BEGIN
DELETE FROM Mov_CxP
WHERE ThePrimaryKey = :PrimaryKey;
END
END

Then, in a IB_Dsql component, use this statement for the SQL property:

EXECUTE PROCEDURE Delete_Em (:Dini)



regards,
Helen Borrie (TeamIBO Support)

** Please don't email your support questions privately **
Ask on the list and everyone benefits
Don't forget the IB Objects online FAQ - link from any page at www.ibobjects.com