How to filter DELETE statement using another table's value in MySQL?

Question

I have not managed to find an example of this. Maybe it's impossible, or I am just not using proper search words.

It's simple: I have a joined table with Foreign keys for events:

CREATE TABLE dbp_Join(
Event INT,
Participant INT,
Location INT,
TST TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY(Event) REFERENCES dbp_Events(ID)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
FOREIGN KEY(Participant) REFERENCES dbp_Participants(ID)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
FOREIGN KEY(Location) REFERENCES dbp_Locations(ID)
    ON UPDATE CASCADE
    ON DELETE CASCADE
)ENGINE = INNODB;

And I have events table:

CREATE TABLE dbp_Events(
ID INT auto_increment,
Name Char(80),
ETime DATETIME NOT NULL,
Details VARCHAR(2000),
E_TST TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY(ID, E_TST)
)ENGINE = INNODB;

I have values in the Join table that reference the events and users attending them. I want to delete all rows in Join table that reference the event happening on 2014-12-12. How do I write that query? Can I? I want to have something like this:

DELETE FROM dpb_Join WHERE DATE(dbp_Events.`ETime`)='2014-12-12';

I have tried

DELETE FROM dbp_Join, dbp_Events WHERE DATE(`ETime`)='2014-12-12';

but it doesn't work, it just empties the whole table.

UPDATE: Tried an example from MySQL DELETE reference that seemed like the right way to do it, but it doesn't delete anything:

DELETE dbp_join FROM dbp_join INNER JOIN dbp_events
WHERE DATE(dbp_events.ETime='2014-12-12');

I can't, I want to clear it's instances, but not delete the event itself. — Megakoresh, Dec 10 '14 at 13:10

score 1 · Accepted Answer · answered Dec 10 '14 at 13:17

I can't see the problem here !?!?

 DROP TABLE IF EXISTS events;

 CREATE TABLE events (event_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,event_date DATE NOT NULL);

 INSERT INTO events (event_date) VALUES ('2014-12-12'),('2014-12-13');

 CREATE TABLE event_participants (event_id INT NOT NULL,participant VARCHAR(12) NOT NULL,PRIMARY KEY(event_id,participant));

 INSERT INTO event_participants VALUES (1,'Adam'),(1,'Bob'),(2,'Adam'),(2,'Charles');

 SELECT * FROM events;
 +----------+------------+
 | event_id | event_date |
 +----------+------------+
 |        1 | 2014-12-12 |
 |        2 | 2014-12-13 |
 +----------+------------+

 SELECT * FROM event_participants;
 +----------+-------------+
 | event_id | participant |
 +----------+-------------+
 |        1 | Adam        |
 |        1 | Bob         |
 |        2 | Adam        |
 |        2 | Charles     |
 +----------+-------------+

 DELETE ep FROM events e JOIN event_participants ep ON ep.event_id = e.event_id WHERE e.event_date = '2014-12-12';
 Query OK, 2 rows affected (0.03 sec)

 SELECT * FROM events;
 +----------+------------+
 | event_id | event_date |
 +----------+------------+
 |        1 | 2014-12-12 |
 |        2 | 2014-12-13 |
 |        3 | 2014-12-12 |
 |        4 | 2014-12-13 |
 +----------+------------+

 SELECT * FROM event_participants;
 +----------+-------------+
 | event_id | participant |
 +----------+-------------+
 |        2 | Adam        |
 |        2 | Charles     |
 +----------+-------------+

Oh so you just put the table containing the dates after the FROM and join the foriegn key table. Basically swap it around. Well the logic of that makes no sense to me, but it works, so thanks! — Megakoresh, Dec 10 '14 at 13:23
This is an inner join. The order of the tables makes no difference. — Strawberry, Dec 10 '14 at 13:36
Yes but apparently the one you put between DELETE and FROM does. Since your solution differs from the last one I tried only by that. — Megakoresh, Dec 11 '14 at 08:03
Yes - you put the things you want to delete in the DELETE bit. Strangely, I don't find that counter-intuitive ;-) — Strawberry, Dec 11 '14 at 10:22

score 1 · Answer 2 · edited May 23 '17 at 12:05

1

The JOINs will make the deletions slow. Use the IN clause instead.

DELETE FROM dbp_Join WHERE Event IN (
    SELECT ID FROM dbp_Events WHERE ETime = DATE('2014-12-12')
)

See also

Delete with Join in MySQL

edited May 23 '17 at 12:05

Community

1
1

answered Dec 10 '14 at 13:34

Franz Holzinger

913
10
20

I do not have the test data. This effect can only be seen with a huge amount of data. And it also depends on the number of event on the same day. If you have 1000 events on the same day (under 3000 events), then probably both solutions will have the same speed. – Franz Holzinger Dec 10 '14 at 14:04
A JOIN is always a multiplication of rows. Each row of the first table must be compared with each row of the second table. m x n records must be read. With the select there is only one query on the second table. The result is stored internally. To improve the speed: INTEGER with unix timestamp should be used instead of DATE. – Franz Holzinger Dec 10 '14 at 14:22
OK - I've just tested it. I'm satisfied that your assertion is in fact true! (Although only appreciable above a million rows, and maybe fractionally poorer than my solution below 1000 rows). The inability to use an index kills it however - and is much more significant than whether a JOIN or an IN is used (that query's still running!). I haven't compared sparse vs dense arrangements. – Strawberry Dec 10 '14 at 14:33

How to filter DELETE statement using another table's value in MySQL?

2 Answers2