Rafael Diaz Rafael Diaz - 20 days ago 5
MySQL Question

MySQL UPDATE on two different tables

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

Answer

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';