mysql - 根据多表条件从表中删除条目

Question

总结:我们需要根据涉及表本身以及单独表的条件从表中删除条目

我已经能够使用两个示例表(称为 table_A 和 table_B)重现我们试图解决的问题。它们的定义如下:

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

表格填充如下:

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

我们要遍历table_A并删除其中的所有条目

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

Answer 1

这应该很简单:

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

引用:Multiple-Table in DELETE Syntax