Error 1175: Delete rows from table1 where PK matches PK from table2

Question

I am trying to delete all rows from table1 that have a matching PK in table2. I am getting error 1175 though my WHERE clause is using a Key. I'm familiar with toggling the safe mode but this should not be an issue as again my WHERE clause does contain the Private Key for both tables. Any suggestions to resolve would be greatly appreciated. Further details below.

table1 structure:

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;

table2 stucture:

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

Delete Query:

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

Action Output Response:

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.

Thank you.

score 0 · Answer 1 · answered Mar 17 '16 at 22:43

0

I think an EXISTS clause might accomplish what you described:

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

answered Mar 17 '16 at 22:43

Rob Paller

7,736
29
26

I had tried exactly that in an earlier iteration but had the same result. I have resolved the issue and will post the answer below for future reference. Regards, – nbayly Mar 18 '16 at 14:55

score 0 · Answer 2 · answered Mar 18 '16 at 14:59

I included a mock condition in the WHERE part of the query to bypass the Safe Update though I still don't know why the previous condition doesn't fulfill the requirement of having a PK in the WHERE.

DELETE FROM table1 WHERE (pkfield IN (SELECT pkfield FROM table2)) and pkfield<>""

The mock condition just checks to confirm the field is not empty which it would never be being the table key.

Error 1175: Delete rows from table1 where PK matches PK from table2

2 Answers2