mauro.stettler mauro.stettler - 7 months ago 10
SQL Question

Query fails due to `safe update mode`

I have the following table:

CREATE TABLE `number_locks` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`number` varchar(255) NOT NULL,
`timeout` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `number` (`number`),
KEY `timeout` (`timeout`)
) ENGINE=InnoDB AUTO_INCREMENT=69146 DEFAULT CHARSET=latin1


Now I would like to run this prepared delete statement in it:

> SET @number="123123";
Query OK, 0 rows affected (0.17 sec)

> PREPARE delete_number_lock FROM 'DELETE FROM number_locks WHERE number=? OR timeout<NOW()';
Query OK, 0 rows affected (0.17 sec)
Statement prepared

> EXECUTE delete_number_lock USING @number;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column


I can't see why it thinks that there is no where condition using a KEY column.

Answer

I've found the quickest way to circumvent that is by adding a condition on the primary key that will always be true, like so:

DELETE 
FROM number_locks 
WHERE id > 0 AND (number=? OR timeout<NOW())
;