mysql - 错误 1175 : Delete rows from table1 where PK matches PK from table2

Question

我正在尝试删除 table1 中与 table2 中具有匹配 PK 的所有行。尽管我的 WHERE 子句使用了键，但我收到错误 1175。我熟悉切换安全模式，但这不应该成为问题，因为我的 WHERE 子句确实包含两个表的私钥。任何解决建议将不胜感激。更多详细信息如下。

表1结构:

CREATE TABLE `table1` (
  `pkfield` varchar(10) NOT NULL,
  `field1` varchar(3) DEFAULT NULL,
  `field2` varchar(1) DEFAULT NULL,
  `field3` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`pkfield`),
  UNIQUE KEY `pkfield_UNIQUE` (`pkfield`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

表2结构:

CREATE TABLE `table2` (
  `pkfield` varchar(10) NOT NULL,
  PRIMARY KEY (`pkfield`),
  UNIQUE KEY `pkfield_UNIQUE` (`pkfield`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

删除查询:

DELETE table1.*, table2.* FROM table1 INNER JOIN table2
WHERE table1.pkfield=table2.pkfield;

Action 输出响应:

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Queries and reconnect.

谢谢。

Answer 1

我认为 EXISTS 子句可能会实现您所描述的目的:

DELETE
FROM table1 t1
WHERE EXISTS 
     (SELECT 'x'
        FROM table2 t2
        where t2.pkfield = t1.pkfield)