DELETE - INNER JOIN - Sub Query

Question

I have 3 tables as you can see:

The relationships between all 3 tables:

• cal_events.id = cf_cal_events.model_id
• cf_cal_events.col_10 = source.ID_ELEMENT

I want to delete data from cal_events and cf_cal_events if ASUPPRIMER = '1'

I know it's not very hard, but I'm not very familiar with JOINS and Sub-queries, but here are my tries to do it for cal_events:

DELETE FROM cal_events
INNER JOIN cf_cal_events ON cal_events.id = cf_cal_events.model_id
INNER JOIN source ON cf_cal_events.col_10 = source.ID_ELEMENT
WHERE source.ASUPPRIMER = '1';

AND

DELETE FROM cal_events
WHERE cal_events.id IN (
    SELECT cf_cal_events.model_id AS cal_events.id
    FROM cf_cal_events
    WHERE cf_cal_events.col_10 IN (
        SELECT ID_ELEMENT AS cf_cal_events.col_10
        FROM source
        WHERE source.ASUPPRIMER = '1'
    )
);

Answer 1

I am going to assume that your queries are correct. You just need to add the aliases to the delete statement:

DELETE cal_events, cf_cal_events
    FROM cal_events INNER JOIN
         cf_cal_events
         ON cal_events.id = cf_cal_events.model_id INNER JOIN
         source
         ON cf_cal_events.col_10 = source.ID_ELEMENT
WHERE source.ASUPPRIMER = '1';

Answer 2

Find multi table syntax from the docs. Something like this

DELETE cal_events,
       cf_cal_events
  FROM cal_events
  JOIN cf_cal_events 
    ON cal_events.id = cf_cal_events.model_id
  JOIN source 
    ON cf_cal_events.col_10 = source.ID_ELEMENT
 WHERE source.ASUPPRIMER = '1';

Answer 3

There's a good example for your problem: How to Delete using INNER JOIN with SQL Server?

seems to me like it should be:

DELETE cal_events, cf_cal_events
FROM cal_events
INNER JOIN cf_cal_events ON cal_events.id = cf_cal_events.model_id
INNER JOIN source ON cf_cal_events.col_10 = source.ID_ELEMENT
WHERE source.ASUPPRIMER = '1';