Error 1054: Unknown column in field list and where clause

Question

I am missing something here, not sure what. please help me.

I am trying to copy the data in vs_smelter_temporary into vs_smelter_ext with a where clause

---

use test;
Update vs_smelter_ext
SET vs_smelter_ext.Certification_Status = vs_smelter_temporary.Certification_Status
where 
vs_smelter_ext.Smelter_Id = vs_smelter_temporary.Smelter_Id
 ;

Answer 1

You need to join to the temporary table:

UPDATE vs_smelter_ext 
     JOIN vs_smelter_temporary ON vs_smelter_ext.Smelter_Id = vs_smelter_temporary.Smelter_Id 
     SET vs_smelter_ext.Certification_Status = vs_smelter_temporary.Certification_Status;