1. 

   ajdjackson, Member

   Hi

   I've been scratching my head with why the following is giving me an error.

   I've created a temporary table as follows:

   CREATE TEMPORARY TABLE cashflow_sum_tmp ( CustName varchar(50), SiteName varchar (50),Period date ,ResSpend float, Sales float, MSpend float, Primary key (SiteName,Period));

   I then insert data from a subquery which works fine but I then attempt to insert or update more data with following code:

   insert into cashflow_sum_tmp (CustName, SiteName ,Period ,ResSpend, Sales , MSpend)

   select

   e.tblCustomer_Name, d.Site_Name, a.Period, 0, sum(a.HSales) as SalesH, 0

   from sales_tmp a

   join hilmark.tblsitesplotsjake c on a.Plot_ID = c.tblContractXIDJobNo join hilmark.tblsitesjake d on c.Site_ID = d.Site_ID join hilmark.tblCustomers_j e on d.Site_Company = e.tblCustomer_ID group by a.Period,d.Site_ID

   on duplicate key update Sales = a.SalesH;

   This compiles ok but when I run the report I get Unknown column 'a.SalesH' in 'field list'

   I've tried on duplicate key update Sales = values(SalesH); etc but with no success.

   Can you help me please?

   Thanks

   Jake
2. 

   myDBR Team, Key Master

   Try:

   on duplicate key update Sales = values(Sales)

   --
   myDBR Team
3. 

   ajdjackson, Member

   Thanks that worked.

   It must have been the only combination I didn't try.

   I don't quite understand how that works: Sales = values(Sales)

   the first Sales to left of = signs I understand to be referring to the column Sales in temp table but where does sum(a.HSales) as SalesH in the query get related to the Sales in the values?

   Cheers

   Jake
4. 

   myDBR Team, Key Master

   The on duplicate key statement always refers to the columns to be updated. In your example the Sales refers to the Sales column and values(Sales) refers to the value from the insert statement. Sales is the fifth column in insert and therefore values(Sales) refers to the fifth column in your insert data, which happens to be the sum(a.HSales).

   --
   myDBR Team

Reply

You must log in to post.