3

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;

enter image description here

2
  • Since your query doesn't include a reference to temporary table a, the engine has no idea what a is. I'm a SQL Server guy, not 100% sure of MySQL syntax for this.
    – RDFozz
    Mar 9 '17 at 20:10
  • How do you reference a temporary table? Again, I am very new at this. Mar 9 '17 at 20:13
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.

0
1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.