我正在尝试使用此查询更新一些记录,这基本上只是一种为客户设置“状态”的方法,考虑到他们的“自上次订单以来的天数”:
UPDATE customers AS c1
INNER JOIN (
SELECT id, DATEDIFF(NOW(), agg.cdt) AS acdt
FROM customers
INNER JOIN
(
SELECT c.id AS cid, max(o.datetime) as cdt
FROM customers AS c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.state = 20
GROUP BY c.id
) AS agg ON customers.id = agg.cid
WHERE account_type IN (1, 2)
AND deleted = 0
AND (account_management_state IN (0, 1, 2) OR account_management_state IS NULL)
) AS c2 ON c1.id = c2.id
SET c1.account_management_state = CASE
WHEN c2.acdt <= 90 THEN 0
WHEN c2.acdt >= 91 AND c2.acdt <= 360 THEN 1
WHEN c2.acdt > 360 OR c2.acdt IS NULL THEN 2
END
WHERE c1.id = c2.id;
但我得到了:
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 Editor and reconnect.
我在最后的WHERE
中使用表键,c1.id
是customers
表键。使用SET SQL_SAFE_UPDATES = 0;
不是一个选项。我还尝试使用WHERE c1.id > 0
但没有效果
请注意,我试图手动运行查询来更改SET SQL_SAFE_UPDATES = 0;
,查询按预期运行,但这应该是一个自动过程
因此,我的选择是:
SQL_SAFE_UPDATES
无法使用李>SQL_SAFE_UPDATES
。(参见侧注)还尝试:
使用相同的ID
UPDATE
...
WHERE c1.id = c1.id ;
在每个子查询之后添加一个巨大的限制(如@Akina所建议的):
UPDATE customers AS c1
INNER JOIN (
SELECT id, DATEDIFF(NOW(), agg.cdt) AS acdt
FROM customers
INNER JOIN
(
SELECT c.id AS cid, max(o.datetime) as cdt
FROM customers AS c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.state = 20
GROUP BY c.id
LIMIT 100000000
) AS agg ON customers.id = agg.cid
WHERE account_type IN (1, 2)
AND deleted = 0
AND (account_management_state IN (0, 1, 2) OR account_management_state IS NULL)
LIMIT 100000000
) AS c2 ON c1.id = c2.id
SET c1.account_management_state = CASE
WHEN c2.acdt <= 90 THEN 0
WHEN c2.acdt >= 91 AND c2.acdt <= 360 THEN 1
WHEN c2.acdt > 360 OR c2.acdt IS NULL THEN 2
END
WHERE c1.id = c2.id;
两个ID的组合:
UPDATE
...
WHERE c1.id > 0 and c2.id > 0;
他们都没有工作。仍然得到Error Code: 1175
这是使用MySQL Python客户端和游标的Python/Flask过程的一部分。我可以使用SQL_SAFE_UPDATES
,只要它是从Python游标完成的。这不起作用:
使用不同的查询(不会抛出错误,只是不会更新任何内容):
connection = db.get_conn()
cursor = connection.cursor()
cursor.execute('SET SQL_SAFE_UPDATES = 0;')
cursor.execute(query) # from the original query
cursor.execute('SET SQL_SAFE_UPDATES = 1;')
使用单个查询(不抛出错误,它不会更新任何内容):
connection = db.get_conn()
cursor = connection.cursor()
cursor.execute('''
SET SQL_SAFE_UPDATES = 0;
UPDATE ...;
SET SQL_SAFE_UPDATES = 1;
''')
使用BEGIN .. END
(我以为我看到了光明,但没有),获得ProgrammingError
:
connection = db.get_conn()
cursor = connection.cursor()
cursor.execute('''
BEGIN
SET SQL_SAFE_UPDATES = 0;
UPDATE ...;
SET SQL_SAFE_UPDATES = 1;
END
''')
我知道这已经有很长时间了,但是今天可能和你我有同样的问题。 我已通过在关闭连接之前提交更改来解决此问题 只需在关闭连接“cnx.commit()之前添加此项” 希望这能有所帮助
相关问题 更多 >
编程相关推荐