Error fulfilling a column when comparing with a lookup table in another schema

Question

I have a problem trying to fulfill a column id 'like' when comparing the values with another ones in a different schema.

This is the code I'm using:

update oldb.incidencias set impact_id = 
    (select impact_id
    from
        newdb.impacts
    where
         olddb.incidencias.impacto = newdb.impacts.impact_type);

And this is the error I get:

Error Code: 1054. Unknown column 'olddb.incidencias.impacto' in 'where clause'

Answer 1

You can try this:

update o
set o.impact_id = n.impact_id
from  oldb.incidencias o
inner join newdb.impacts n 
  on o.impacto = n.impact_type

Or

update oldb.incidencias o
inner join newdb.impacts n 
  on o.impacto = n.impact_type
set o.impact_id = n.impact_id