A Particular Case of MySQL Error 1093 - Can't specify target table for update in FROM clause

Question

The following query (which roughly translates to "if a customer has only one radio then it is upstream from the router")

UPDATE Devices AS A 
INNER JOIN Devices AS B ON A.CustomerID = B.CustomerID 
SET A.Uplink=B.IPAddress 
WHERE A.DeviceType='Router'  AND  B.DeviceType='Radio' 
AND (
  select count(temp.CustomerID) as total 
  FROM Devices AS temp 
  where temp.DeviceType = 'Radio' AND temp.CustomerID=A.CustomerID
) = 1;

gives error 1093 as soon as you add the COUNT query. The same thing happens when you do a regular UPDATE (without the INNER JOIN) and put the reference to the radio in a SELECT statement in the SET clause after "SET A.Uplink=".

There is currently a feature request to remove this limitation at https://bugs.mysql.com/bug.php?id=23353 and there are simple workarounds for some cases such as this but I haven't found a way to implement my particular example. Any ideas?

Dustin Soodak

score 1 · Accepted Answer · answered Apr 28 '20 at 00:14

Remove the subquery from the WHERE clause and join it like this:

UPDATE Devices AS A 
INNER JOIN Devices AS B ON A.CustomerID = B.CustomerID 
INNER JOIN (
  SELECT CustomerID, COUNT(CustomerID) AS total 
  FROM Devices
  WHERE DeviceType = 'Radio'
  GROUP BY CustomerID
) t ON t.CustomerID = A.CustomerID
SET A.Uplink = B.IPAddress 
WHERE A.DeviceType='Router' AND B.DeviceType='Radio' AND t.total = 1;

Or:

UPDATE Devices AS A 
INNER JOIN Devices AS B ON A.CustomerID = B.CustomerID 
INNER JOIN (
  SELECT CustomerID 
  FROM Devices
  WHERE DeviceType = 'Radio'
  GROUP BY CustomerID
  HAVING COUNT(CustomerID) = 1
) t ON t.CustomerID = A.CustomerID
SET A.Uplink = B.IPAddress 
WHERE A.DeviceType='Router' AND B.DeviceType='Radio';

A Particular Case of MySQL Error 1093 - Can't specify target table for update in FROM clause

1 Answers1