MySQL您正在使用安全更新模式，并且您试图更新一个没有WHERE的表

Question

我一直从mySQL收到这个错误消息

”错误代码：1175。您正在使用安全更新模式，并且您试图更新一个没有使用KEY列的WHERE的表要禁用安全模式，请在首选项中切换选项-

我使用的查询是：

 UPDATE table1 a INNER JOIN table2 asa ON a.ID = asa.Table1Id SET a.ReferenceID = asa.ReferenceID WHERE a.ID > 0 AND asa.ID > 0 

我确实在ID上有两个表的where子句，这两个表都是主键。

禁用SQL\u安全更新不是一个选项。

编辑：MySQL版本是v5。6.

Answer 1

即使您正在使用KEYS，我猜KEYS没有正确使用。MySQL留档说。

如果启用了此变量，则不使用WHERE子句或LIMIT子句中的键的UPDATE和DELETE语句将产生错误。这使得可以捕获未正确使用键的UPDATE和DELETE语句，这些语句可能会更改或删除大量行。

即使在WHERE条件下，您下面的查询也会影响相同数量的行。我想ID的所有值都大于0对吗？如果是这样，那么这就是你出错的原因。

 UPDATE table1 a 
 INNER JOIN table2 asa 
 ON a.ID = asa.Table1Id 
 SET a.ReferenceID = asa.ReferenceID 
 --WHERE a.ID > 0 AND asa.ID > 0 

一种方法是按照建议SETSQL_SAFE_UPDATES=0；（禁用并尝试）。或者尝试改进WHERE条件。如果是单个表，也可以使用LIMIT。

Answer 2

MySQL 5.6在执行UPDATE语句和JOIN

所以，与其

UPDATE table1 a
INNER JOIN table2 asa
ON a.ID = asa.Table1Id
SET a.ReferenceID = asa.ReferenceID
WHERE a.ID > 0 AND asa.ID > 0

您必须根据需要编写尽可能多的查询，例如：

UPDATE table1 a
SET a.ReferenceID = <The corresponding value in table 2>
WHERE a.ID = <The corresponding ID>

这对于类型来说非常烦人，可以使用动态SQL构建更新查询：

SELECT CONCAT('UPDATE table1 a SET a.ReferenceID = ', asa.ReferenceID, ' WHERE a.ID = ', t.ID, ';')
FROM table1 t
INNER JOIN table2 asa
ON t.ID = asa.Table1Id;

例如：

模式（MySQL v5.6）

CREATE TABLE test
(
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    foo VARCHAR(255)
);

CREATE TABLE test2
(
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    id_test INT NOT NULL,
    foo VARCHAR(255),
    FOREIGN KEY (id_test) REFERENCES test(id)
);

INSERT INTO test (foo) VALUES ('hello'), ('world');

INSERT INTO test2 (id_test, foo) VALUES (1, 'bar'), (2, 'baz');

问题#1

SELECT CONCAT('UPDATE test t SET t.foo = ''', t2.foo, ''' WHERE t.id = ', t.id, ';') AS 'sql query'
FROM test t
INNER JOIN test2 t2
ON t.id = t2.id_test;

这将产生：

UPDATE test t SET t.foo = 'bar' WHERE t.id = 1;
UPDATE test t SET t.foo = 'baz' WHERE t.id = 2;

现在可以使用输出手动更新不同的行

DB-Fiddle视图