CODEWITHSUNDEEP
mysqlsql
Sandeep
Sandeep

Reputation: 1401

Delete entries from a table based on multi-table conditions

Summary: We need to delete entries from a table based on conditions involving the table itself as well as a separate table

I have been able to recreate the issue that we are trying to solve using two sample tables, called table_A and table_B. Their definitions are as follows:

CREATE TABLE table_A
(
  id  INT UNSIGNED AUTO_INCREMENT,
  name  VARCHAR(64) NOT NULL,
  date  DATE NOT NULL,
  otherFields_A VARCHAR(64) NOT NULL,
  -- many other fields in the table (different from table_B)
  -- but not relevant to the question being asked

  PRIMARY KEY(id)
);



CREATE TABLE table_B
(
  id  INT UNSIGNED AUTO_INCREMENT,
  name  VARCHAR(64) NOT NULL,
  date  DATE NOT NULL,
  otherFields_B VARCHAR(64) NOT NULL,
  -- many other fields in the table (different from table_A)
  -- but not relevant to the question being asked

  PRIMARY KEY(id)
);

The tables are populated as follows:

INSERT INTO table_A(name, date) VALUES('John Doe', '2018-01-01'), ('John Doe', '2018-07-01'), ('John Doe', '2018-12-01'), ('Mary Smith', '2018-08-02'), ('Andy Roberts', '2018-07-02'), ('John Doe', '2018-12-02');

INSERT INTO table_B(name, date) VALUES('Keith Miller', '2018-01-03'), ('Mary Smith', '2018-07-02'), ('John Doe', '2018-07-30');

We want to traverse table_A and delete all entries for which

table_B.name= table_A.name
AND
table_B.date >= table_A.date

Upvotes: 1

Views: 40

Answers (1)

GMB
GMB

Reputation: 222582

This should as simple as :

DELETE table_A
FROM table_A
INNER JOIN table_B 
    ON table_B.id = table_A.id
    AND table_B.date >= table_A.date

Demo on DB Fiddle

Reference : Multiple-Table in DELETE Syntax

Upvotes: 1

Related Questions