MySQL query not allowed in the FROM clause-CodePudding

This is my query

UPDATE table1 SET `value` = ((SELECT IF(value<1,value   "1",value) FROM table1 WHERE name = "name1")   (SELECT value FROM table2 WHERE settings = "settings1") / 8 / 60) MOD 999 WHERE name = "name1";

When I run this on XAMPP Local it works perfect, when I run it on a fresh Installed WebServer I do get this error.

MySQL reports: Documentation

#1093 - The use of the target table 'table1' to be updated is not allowed in the FROM clause.

Any idea why this is happening or is there a better query?

I do not know how to solve this.

CodePudding user response：

Your error message clearly tells you the issue. That is, if you're doing an UPDATE/INSERT/DELETE on a table, you can't reference that table in an inner query.

Instead of using your query:

UPDATE 
  table1 
SET
  `value` = (
    (SELECT 
      IF(VALUE < 1, VALUE   "1", VALUE) 
    FROM
      table1 
    WHERE NAME = "name1")   
    (SELECT 
      VALUE 
    FROM
      table2 
    WHERE settings = "settings1") / 8 / 60
  ) MOD 999 
WHERE NAME = "name1" ;

Use the following:

UPDATE 
  table1 
SET
  `value` = (
    (SELECT 
      IF(VALUE < 1, VALUE   "1", VALUE) 
    FROM
      (SELECT * FROM  table1) a 
    WHERE NAME = "name1")   
    (SELECT 
      VALUE 
    FROM
      table2 
    WHERE settings = "settings1") / 8 / 60
  ) MOD 999 
WHERE NAME = "name1" ;

This way you will be fetching all records from table1 in the form of a subtable (SELECT * FROM table1) a, this should solve your problem.