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
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.
coalesce() to leave
no2 unchanged if no corresponding value is found in
Answered By – Bohemian
Answer Checked By – Gilberto Lyons (BugsFixing Admin)