UPDATE with INNER JOIN is not updating

Question

I'm trying to transfer the impact value from an old table (esp) to a new table (esm). Both table share similar structures:

esp
+--------+------+----------------------------
|codeNorm|impact|Other columns i dont care...
+--------+------+----------------------------

esm
+--------------+------+--------------------------------------
|codeMasterNorm|impact|Other different columns i dont care...
+--------------+------+--------------------------------------

First i created a SELECT query that brings only the records that have different impact between them (impact can be NULL):

SELECT esm.codeMasterNorm, esm.impact, esp.codeNorm, esp.impact FROM esm
INNER JOIN esp ON esp.codeNorm = esm.codeMasterNorm AND IFNULL(esp.impact, 0) <> IFNULL(esm.impact, 0);

This returned:

163 rows in set (0.75 sec)

Then i created the UPDATE/INNER JOIN query

UPDATE esm
INNER JOIN esp ON esp.codeNorm = esm.codeMasterNorm AND IFNULL(esp.impact, 0) <> IFNULL(esm.impact, 0)
SET esm.impact = esp.impact;

This returned:

Query OK, 163 rows affected (1.35 sec) Rows matched: 163 Changed: 163 Warnings: 0

But when i re-run the SELECT query again, this return the same result:

163 rows in set (1.25 sec)

Any ideas of why is not updating the records?

Answer 1

When executing UPDATE JOIN queries; if the join is not 1:1, the source of assigned value is not defined.

In this particular case, while the join was technically 1:1 due to the a.x != b.x condition, the update created different join pairs. The 1:1 that was assumed without that condition was not true.