mysql - How to update target table in FROM clause? -

i prepared following sql statement in order update field master_id of staging table id of corresponding row of master table. master contains objects unique location. therefore 1 id @ maximum should returned if @ all.

update staging  set master_id = (   select m.id    master m, staging s   m.lat = s.lat    , m.lon = s.lon ); 

the query fails you can't specify target table 'staging' update in clause. how can fix this?

also think query can optimized using join. if can greatful!
here try on joining both tables:

update staging s inner join master m on s.lat = m.lat , s.lon = m.lon set s.master_id = m.id;  query ok, 0 rows affected (0.38 sec) rows matched: 14976  changed: 0  warnings: 0 

next try:

update staging s  inner join (   select id, lat, lon   master ) m on s.lat = m.lat , s.lon = m.lon set s.master_id = m.id;  query ok, 0 rows affected (0.35 sec) rows matched: 14976  changed: 0  warnings: 0 

please mind, using mysql if affects syntax of solution.

---

to confirm there matching rows (each <-- intended) query should proof fact.

select count(*) master m, staging s t.lat = s.lat , t.lon = s.lon;  +----------+ | count(*) | +----------+ |    14976 | +----------+ 1 row in set (0.05 sec) 

you're setting every master_id in table this:

  select m.id    master m, staging s   m.lat = s.lat    , m.lon = s.lon 

contrary think, return many rows. try standalone query! sql doesn't magically understand want m.id has lat , lon matching row you're updating.

sqlfiddle down @ moment, can't double-check, might want this:

update staging  set master_id = (   select m.id    master m   -- m.lat,m.lon  lat,lon values **in row you're updating**   m.lat = staging.lat    , m.lon = staging.lon ); 

note set null every master_id value in staging no matching (lat,lon) found. may or may not issue.