1

I'm trying to update a database in a 3rd party application. Here are the schema of the two tables affected:

Table 'camera' 
columns:  deviceID, connectionState

Table 'cameraRecording' 
columns: deviceID, recordToVolumeID

I've tried both of these queries, but receive the same error. 

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.

I'm no expert in SQL and don't use it very often, but I'm pretty sure this should work. I'm guessing the application has a trigger or function already set... Is there a way to perform this task? I've read many other posts of others with the error, but most involved them trying to create a trigger.

Nabeel Ahmed
  • 18,328
  • 4
  • 58
  • 63
faction918
  • 53
  • 1
  • 2
  • 5
  • 1
    http://stackoverflow.com/questions/1582683/mysql-trigger-stored-trigger-is-already-used-by-statement-which-invoked-stored-t?rq=1 – Mihai Sep 27 '13 at 02:35

1 Answers1

2

Can you declare a variable then use the value?

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

update cameraRecording 
SET recordToVolumeID='STRING' 
WHERE deviceID = @id;
John Bingham
  • 1,996
  • 1
  • 12
  • 15