MySQL Workbench - UPDATE statement using WHERE clause

Question

I'm trying to update a table called 'Rural' - column name 'NO2', with data from another table called 'new_no2' - column name 'NO2'. see the code below

update Rural
set Rural.NO2 = new_no2.NO2
where Rural.Year = new_no2.Year
and Rural.Site = new_no2.Site;

However, I keep having the error code: 1054. Unknown column 'new_no2.Year' in 'Where clause'

I went through a lot of tutorials and tried loads of things like make sure the table name is correct and so on, but for some reason, it does not work.

It will be great if someone can give some clarity on what I might be doing wrong.

by the way, I'm using MySQL workbench version 8 for MacBook

Thanks Bruno

Answer 1

Use a sub query:

update Rural set
Rural.NO2 = coalesce((
    select new_no2.NO2
    from new_no2
    where Rural.Year = new_no2.Year
    and Rural.Site = new_no2.Site), no2)

See live demo.

Use coalesce() to leave no2 unchanged if no corresponding value is found in new_no2.