如何从条件中删除2个表

Question

I want to build a query to delete data from 2 tables but wiht some conditions like if the cree_facture.refe ='EST1' delete from cree_devis whith cree_devis.refe='EST1' and if the refe ='INV1' delete from cree_facture with the cree_facture.refe='INV1'

I try with this 2 query bu the problem delete all data from table not has the condition

DELETE FROM `cree_devis`, `cree_facture` 
USING `cree_devis`,`cree_facture` 
WHERE `cree_devis`.`cree_par` = `cree_facture`.`cree_par` 
  AND (`cree_devis`.`refe` = 'INV1'  OR `cree_facture`.`refe` = 'INV1' )

and

DELETE cree_devis, cree_facture 
FROM cree_devis 
INNER JOIN cree_facture ON (cree_devis.cree_par=cree_facture.cree_par)
WHERE cree_devis.refe = 'EST1' 
OR cree_facture.refe = 'EST1';

Answer 1

First of all you can not delete in 2 tables at the same time using a join.

You probably want this:

DELETE FROM CREE_DAVIS T1 WHERE T1.REFE = 'EST1' AND EXISTS (SELECT 1 FROM CREE_FACTURE T2 WHERE T1.CREE_PAR = T2.CREE_PAR AND T2.REFE = 'EST1')

Explaining: Delete from Table 1 every row for which there exists a row with matching CREE_PAR in the other table, while both have REFE = 'EST1'

Hope this is what you want.

You can now also make the other delete in the same way, using INV1 and changing the table names in the inner and outer query.