CODEWITHSUNDEEP
mysqlsqlinner-joindelete-row
rand
rand

Reputation: 183

DELETE - INNER JOIN - Sub Query

I have 3 tables as you can see: enter image description here

The relationships between all 3 tables:

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'
    )
);

Upvotes: 0

Views: 2338

Answers (3)

DougieHauser
DougieHauser

Reputation: 470

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';

Upvotes: 0

Andreas Wederbrand
Andreas Wederbrand

Reputation: 39951

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';

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269623

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';

Upvotes: 1

Related Questions