Updating a table from a temporary table where clause error in MySql Community

Question

I am trying to update an existing table named "residential_summary" from a temporary table called "a". I can query the temporary table a but when I try to use it in an update statement I get

Error Code: 1054 Unknown column 'a.meter' in 'where clause'

Is this something that you just cant do or am I somehow misinterpreting the syntax for Update? Why is my column unknown? Please elaborate because I am new at databases. Here is my code and a screenshot.

UPDATE residential_summary
SET residential_summary.load_factor = a.load_factor
WHERE a.meter = residential_summary.meter;

Answer 1

SQL queries always explicitly reference their tables by name, apart from any references to the columns in the tables. Since your query doesn't include a reference to the 'a' table explicitly as a table, the engine has no idea what a is. I'm a SQL Server guy, but based on some quick Googling of "MySQL update join", looks like the statement should be:

UPDATE residential_summary
         INNER JOIN a ON residential_summary.meter = a.meter
SET residential_summary.load_factor = a.load_factor;

The UPDATE ... INNER JOIN ... tells the engine that this query will be working with the residential_summary and a tables. The INNER JOIN ties residential_summary and a together based on the values of meter in both tables, so that only residential_summary rows that have a matching row in a will be updated.

Answer 2

Your query misses mentioning of a table after update. This should work (I only added a)

UPDATE residential_summary, a
SET residential_summary.load_factor = a.load_factor
WHERE a.meter = residential_summary.meter;