mysql - 错误 1442 : Can't update table in stored function/trigger

Question

我正在尝试更新第 3 方应用程序中的数据库。以下是受影响的两个表的架构:

Table 'camera' 
columns:  deviceID, connectionState

Table 'cameraRecording' 
columns: deviceID, recordToVolumeID 

我已经尝试了这两个查询，但收到相同的错误。

mysql> update cameraRecording 
       SET recordToVolumeID='STRING' 
       WHERE deviceID 
       IN (select deviceID from camera WHERE connectionState=1);

ERROR 1442 (HY000): Can't update table 'camera' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

.

mysql> update cameraRecording a 
       JOIN camera b 
       ON a.deviceID=b.deviceID AND b.connectionState=1 
       SET recordToVolumeID='STRING';

ERROR 1442 (HY000): Can't update table 'camera' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

我不是 SQL 方面的专家，也不经常使用它，但我很确定这应该行得通。我猜应用程序已经设置了触发器或功能...有没有办法执行此任务？我读过许多其他人的错误帖子，但大多数都涉及他们试图创建触发器。

Answer 1

你能声明一个变量然后使用它的值吗？

DECLARE @id int;
SELECT @id = deviceID from camera WHERE connectionState=1;

update cameraRecording 
SET recordToVolumeID='STRING' 
WHERE deviceID = @id;