At 12:13 PM 20/07/2012, aaroncg_98 wrote:

>Hi,
>
>I'm newbie with Firebird. Now I try to delete some orphand records using:
>
>DELETE CUEN_DET01.* FROM CUEN_DET01 LEFT JOIN FACTF01 ON (CUEN_DET01.NO_FACTURA = FACTF01.CVE_DOC) AND (CUEN_DET01.CVE_CLIE = FACTF01.CVE_CLPV)
>WHERE FACTF01.CVE_DOC Is Null
>
>without a good result.

The SQL DELETE statement deletes rows from actual tables, never from output sets (such as your joined set).

DELETE does not take column arguments (your "DELETE CUEN_DET01.* FROM..." is therefore wrong syntax). In DSQL the syntax is

DELETE FROM CUEN_DET01 WHERE ...[search conditions]

You can do what you want in one of three ways:

1. As suggested by Set, use an existence subquery to define the search conditions;

OR
2. Create a view of your joined set and apply triggers to make the view updatable;

OR
3. Write a stored procedure that loops through variables returned from your joined set and send a DELETE FROM CUEN_DET01 WHERE ... statement for each row that meets the conditions.

./heLen