0

We want to be able to delete entries from a MySQL table, based on deletion criteria set in another table. Let me explain with an example.
I have two tables defined as follows:

CREATE TABLE base_tbl
(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  f1 VARCHAR(8),
  f2 VARCHAR(8),
  
  PRIMARY KEY (id)
);


CREATE TABLE del_criteria_tbl
(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  f3 VARCHAR(8),
  f4 VARCHAR(8),
  
  PRIMARY KEY (id)
);

base_tbl has the data and del_criteria_tbl has the criteria for deleting entries from base_tbl.

I populate the tables as follows:

INSERT INTO base_tbl(f1, f2) VALUES ('ABC', '123C57'),
('ABC', '532B49'), ('DEF', '397F45'),
('DEF', '684G65'), ('GHI', '793A86'),
('GHI', '541H32');  

and

INSERT INTO del_criteria_tbl(f3, f4) VALUES ('ABC', '532B49'),
('ABC', '813E89'), ('DEF', '397F45'),
('GHI', '541H32');

Obviously:

mysql>SELECT * FROM base_tbl;
+----+------+--------+
| id | f1   | f2     |
+----+------+--------+
|  1 | ABC  | 123C57 |
|  2 | ABC  | 532B49 |
|  3 | DEF  | 397F45 |
|  4 | DEF  | 684G65 |
|  5 | GHI  | 793A86 |
|  6 | GHI  | 541H32 |
+----+------+--------+


mysql>SELECT * FROM del_criteria_tbl;
+----+------+--------+
| id | f3   | f4     |
+----+------+--------+
|  1 | ABC  | 532B49 |
|  2 | ABC  | 813E89 |
|  3 | DEF  | 397F45 |
|  4 | GHI  | 541H32 |
+----+------+--------+

I would like to define a succinct and efficient SQL operation that executes the following pseudo-SQL logic:

DELETE FROM base_tbl WHERE base_tbl.f1 = del_criteria_tbl.f3 AND base_tbl.f2 = del_criteria_tbl.f4

After the operation is executed, SELECT * FROM base_tbl should yield:

+----+------+--------+
| id | f1   | f2     |
+----+------+--------+
|  1 | ABC  | 123C57 |
|  4 | DEF  | 684G65 |
|  5 | GHI  | 793A86 |
+----+------+--------+
3

A simple solution is using IN clause in the following way:

DELETE FROM base_tbl 
WHERE (f1, f2) in (select f3, f4 from del_criteria_tbl)
1

You can use a MYSQL specific construction like:

DELETE a.*
FROM base_tbl a
JOIN  del_criteria_tbl b
    ON a.f1 = b.f3
    AND a.f2 = b.f4

or use a standard variant:

DELETE 
FROM base_tbl a
WHERE EXISTS (
    SELECT 1
    FROM  del_criteria_tbl b
    WHERE a.f1 = b.f3
    AND a.f2 = b.f4
) 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.