INNER JOIN in DELETE FROM TABLE

[Primus]

Hi.

Is the INNER JOIN supported in Base’s embedded HSQLDB or does it have a special syntax?

I want to delete all rows in T_CRENEAUX when AFF_TYPE = 2 in T_AFFECTATIONS

Following various tutorials, I tried this:

DELETE FROM T_CRENEAUX C 
JOIN T_BENEFICIAIRES B ON C.BEN_ID = B.BEN_ID
JOIN T_AFFECTATIONS A ON B.BEN_ID = A.BEN_ID
WHERE AFF_TYPE = 2

And this:

DELETE C FROM T_CRENEAUX C 
JOIN T_BENEFICIAIRES B ON C.BEN_ID = B.BEN_ID
JOIN T_AFFECTATIONS A ON B.BEN_ID = A.BEN_ID
WHERE AFF_TYPE = 2

And the same, with INNER before JOIN: error messages (unexpected JOIN, unexpected C).

I tried like; it’s works but it deleted all the rows (AFF_TYPE = 1 and AF_TYPE = 2)!

DELETE FROM T_CRENEAUX
WHERE T_CRENEAUX.BEN_ID = T_BENEFICIAIRES.BEN_ID
AND T_AFFECTATIONS.BEN_ID = T_BENEFICIAIRES.BEN_ID
AND T_AFFECTATIONS.AFF_TYPE = 2

Thanks.

[Villeroy]

1. Start with a query selecting primary keys from T_CRENEAUX you want to delete.
2. DELETE FROM T_CRENEAUX WHERE ID IN (SELECT T_CRENEAUX.ID FROM T_CRENEAUX JOIN ... WHERE ...)

[Primus]

Ok, it’s works…

DELETE FROM T_CRENEAUX C WHERE CRE_ID IN (
SELECT C.CRE_ID
FROM T_CRENEAUX C
JOIN T_BENEFICIAIRES B ON C.BEN_ID = B.BEN_ID
JOIN T_AFFECTATIONS A ON B.BEN_ID = A.BEN_ID
WHERE AFF_TYPE = 2)

Thanks.