SQL error using INNER JOIN function to insert values

Question

I'm having a problem with my simple SQL code as I'm just a beginner. I'm trying to retrieve the average value of outbound-cost-pal for different countries and write this in every corresponding line of Warehouse.

My code is the following:

INSERT INTO Warehouse (`inbound-cost-pal`)
SELECT cost.`average`
FROM Warehouse AS wh
INNER JOIN (
    SELECT  
    AVG     (`warehouse-cost-table`.`outbound-cost-pal`) AS `average`
    FROM    `warehouse-cost-table` 
            )cost
ON wh.`location-tariff-code` = LEFT(cost.`country`,2)
;

I'm receiving the error message 'Unknown column 'cost.country' in 'on clause''.

update your question add a proper data sample and the expected result ... — ScaisEdge, Nov 12 '19 at 13:32
Including '-' within a table/column identifier is a cataclysmically bad idea — Strawberry, Nov 12 '19 at 14:37

score 0 · Accepted Answer · answered Nov 12 '19 at 13:34

You forgot country in the select of subquery cost

INSERT INTO Warehouse (inbound-cost-pal)
SELECT cost.average
FROM Warehouse AS wh
INNER JOIN (
    SELECT  
    AVG     (warehouse-cost-table.outbound-cost-pal) AS average,
            country
    FROM    warehouse-cost-table 
            )cost
ON wh.location-tariff-code = LEFT(cost.country,2)
;

score 0 · Answer 2 · answered Nov 12 '19 at 13:35

if you need an avg() for each country then you need also country in subquery

INSERT INTO Warehouse (`inbound-cost-pal`)
SELECT cost.`average`
FROM Warehouse AS wh
INNER JOIN (
    SELECT   `warehouse-cost-table`.`country`,
    AVG(`warehouse-cost-table`.`outbound-cost-pal`) AS `average`
    FROM    `warehouse-cost-table` 
    GROUP BY `warehouse-cost-table`.`country`
            ) cost
ON wh.`location-tariff-code` = LEFT(cost.`country`,2)
;

otherwise if you don't need an avg() for country but the overall avg then you could use cross join instead of inner join

INSERT INTO Warehouse (`inbound-cost-pal`)
SELECT cost.`average`
FROM Warehouse AS wh
CROSS JOIN (
    SELECT  
    AVG(`warehouse-cost-table`.`outbound-cost-pal`) AS `average`
    FROM    `warehouse-cost-table` 
            ) cost
  ;

and you should avoid space between function name and brackets .. AVG()

SQL error using INNER JOIN function to insert values

2 Answers2