delete all results of a select statement

Question

I want to delete all results selected by this select statement, how do I do that?

SELECT
    *
FROM
    db.wp_term_taxonomy A,
    db.wp_terms B
WHERE
    A.taxonomy = 'post_tag' AND B.term_id = A.term_id

possible duplicate of [How do I delete from multiple tables using INNER JOIN in SQL server](http://stackoverflow.com/questions/783726/how-do-i-delete-from-multiple-tables-using-inner-join-in-sql-server) — Preet Sangha, Jun 24 '13 at 11:49
You can achieve "deleting the rows" by deleting from either table, so there's no join, or from both tables. Which is it? — Bohemian, Jun 24 '13 at 12:00

score 2 · Answer 1 · answered Jun 24 '13 at 11:54

2

DELETE B.* FROM db.wp_terms B
INNER JOIN db.wp_term_taxonomy A  ON B.term_id = A.term_id
WHERE (A.taxonomy = 'post_tag');


DELETE FROM db.wp_term_taxonomy
WHERE taxonomy = 'post_tag'

answered Jun 24 '13 at 11:54

El_L

355
2
8
22

score 1 · Answer 2 · answered Jun 24 '13 at 11:46

1

DELETE
FROM
db.wp_term_taxonomy A,
db.wp_terms B
WHERE
A.taxonomy = 'post_tag' AND B.term_id = A.term_id

answered Jun 24 '13 at 11:46

Wouter van der Houven

448
3
9

I didn't know you could delete across tables like this. – Preet Sangha Jun 24 '13 at 11:48
Can't delete entries like this. – Nagendra Rao Jun 24 '13 at 11:49
@Rao why do you think so? do you want to delete the records from both tables? – tftd Jun 24 '13 at 11:50
@tftd Yes, I want to delete only the rows and NOT the table from both tables, ie., all the rows returned by above `SELECT` statement – Nagendra Rao Jun 24 '13 at 11:52

score 1 · Accepted Answer · answered Jun 24 '13 at 12:01

 DROP TABLE IF EXISTS wp_term_taxonomy;
 CREATE TABLE wp_term_taxonomy(term_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, taxonomy VARCHAR(12) NOT NULL UNIQUE);

 DROP TABLE IF EXISTS wp_terms;
 CREATE TABLE wp_terms(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, term_id INT NOT NULL);

 INSERT INTO wp_term_taxonomy VALUES (1,'post_box'),(2,'post_bag');
 INSERT INTO wp_terms VALUES (1,1),(2,1),(3,2),(4,2);

 DELETE a,b FROM wp_term_taxonomy a JOIN wp_terms b ON b.term_id = a.term_id WHERE a.taxonomy = 'post_bag';

 SELECT * FROM wp_term_taxonomy;
 +---------+----------+
 | term_id | taxonomy |
 +---------+----------+
 |       1 | post_box |
 +---------+----------+

 SELECT * FROM wp_terms;
 +----+---------+
 | id | term_id |
 +----+---------+
 |  1 |       1 |
 |  2 |       1 |
 +----+---------+

As an alternative, see the CASCADE options of InnoDB tables

Thanks, this worked for me. `DELETE a,b FROM db.wp_term_taxonomy a JOIN db.wp_terms b ON b.term_id = a.term_id WHERE a.taxonomy = 'post_tag';` — Nagendra Rao, Jun 24 '13 at 12:14

delete all results of a select statement

3 Answers3