Search code examples
mysqlsqlinnodb

MySQL UPDATE: Every derived table must have its own alias

The following query in MySQL:

UPDATE e_element e1
 INNER JOIN
 (
 SELECT DVAL
 FROM e1
  INNER JOIN  e_element e2
   ON e1.ENT_OID = e2.ENT_OID
  WHERE
   e2.META_OID = 336810 AND
   e1.META_OID = 359462 AND
   e1.DVAL = '0001-01-01 00:00:00'
 )
SET e1.DVAL = e2.DVAL;

Throws the following error: 

Error Code: 1248
Every derived table must have its own alias

I have tried a few different options with two select statements but I can never achieved the result I want which is to copy the value from e2 to e1 given those conditions

Solution

  • Your query has multiple errors . . . a curly brace, no on clause, an inappropriate reference to a table alias, a where clause in the wrong place.

    Perhaps you intend:

    UPDATE e_element e1 INNER JOIN
       e_element e2
       ON e1.ENT_OID = e2.ENT_OID
    SET e1.DVAL = e2.DVAL
WHERE e2.META_OID = 336810 AND
      e1.META_OID = 359462 AND
      e1.DVAL = '0001-01-01 00:00:00';